Hello shaun, May be with subselects you can do what you want:
SELECT C.*,(select SUM(E.Amount) from Expenses E where E.Claim_ID = C.Claim_ID)AS Amount, (select SUM(M.Mileage) from Mileage M where M.Claim_ID = C.Claim_ID) AS Mileage FROM Claims C WHERE C.Claimant_ID = '1' st> Hi, st> I have ( among others ) three tables in my database: Claims, Expenses and st> Mileage. A claim can contain many expense entries and many mileage entries. st> I am using the follwing query to show the total expenses and mileage per st> claim for a particulare user: st> SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage st> FROM Claims C st> LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID st> LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID st> WHERE C.Claimant_ID = '1' st> GROUP BY C.Claim_ID st> The problem is if there are two mileage entries and one expense entry the st> expense total is doubled (and vice versa), can some explain this to me st> please? st> Thanks for your help. st> TABLE DEFINITIONS: mysql>> desc Claims; st> +-----------------+-------------+------+-----+---------+----------------+ st> | Field | Type | Null | Key | Default | Extra | st> +-----------------+-------------+------+-----+---------+----------------+ st> | Claim_ID | int(11) | | PRI | NULL | auto_increment | st> | Claimant_ID | int(11) | | | 0 | st> | Description | varchar(50) | | | | st> | Status | varchar(50) | YES | | Open | st> | Submission_Date | datetime | YES | | NULL | st> | Approval_Date | datetime | YES | | NULL | st> | Approver_ID | int(11) | YES | | NULL | st> +-----------------+-------------+------+-----+---------+----------------+ st> 7 rows in set (0.00 sec) mysql>> desc Expenses; st> +---------------------+---------------+------+-----+---------+----------------+ st> | Field | Type | Null | Key | Default | Extra st> | st> +---------------------+---------------+------+-----+---------+----------------+ st> | Expense_ID | int(11) | | PRI | NULL | st> auto_increment | st> | Claim_ID | int(11) | YES | | NULL | st> | st> | Description | varchar(50) | YES | | NULL | st> | st> | Expense_Category_ID | int(11) | YES | | NULL | st> | st> | Insertion_Date | date | YES | | NULL | st> | st> | Project_ID | int(11) | YES | | NULL | st> | st> | Amount | decimal(10,2) | YES | | NULL | st> | st> | Rate_ID | int(11) | YES | | NULL | st> | st> | Supplier_ID | int(11) | YES | | NULL | st> | st> | Receipt | varchar(10) | YES | | NULL | st> | st> | Receipt_Date | varchar(10) | YES | | NULL | st> | st> | VAT_Receipt | varchar(10) | YES | | NULL | st> | st> | VAT_Amount | decimal(10,2) | YES | | NULL | st> | st> +---------------------+---------------+------+-----+---------+----------------+ st> 13 rows in set (0.00 sec) mysql>> desc Mileage; st> +----------------+--------------+------+-----+------------+----------------+ st> | Field | Type | Null | Key | Default | Extra | st> +----------------+--------------+------+-----+------------+----------------+ st> | Mileage_ID | int(11) | | PRI | NULL | auto_increment | st> | Claim_ID | int(11) | | | 0 | st> | Project_ID | int(11) | | | 0 | st> | Insertion_Date | date | | | 0000-00-00 | st> | Description | varchar(255) | | | | st> | Start_Mileage | int(11) | | | 0 | st> | End_Mileage | int(11) | | | 0 | st> | Mileage | int(11) | | | 0 | st> +----------------+--------------+------+-----+------------+----------------+ st> 8 rows in set (0.00 sec) mysql>> -- Best regards, Krasimir_Slaveykov, 18 Март 2005 г., 12:54:56 mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-------------------------------------------------| |///// **** ***** ***** *** **** ***** ** /| |//// *** *** *** *** *** *** ** *** **** //| |/// *** *** ***** ***** *** *** ***** *** ///| |// *** *** *** *** *** *** ** *** *** ////| |/ **** *** *** *** **** ***** *** /////| |--------------- www.office1.bg ------------------| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
