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