Show us your table structures and the data they contain. It appears your WHERE clause probably acting correctly. But without data, we can determine that. -----Original Message----- From: paul wilczynski [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 07, 2002 11:49 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Can't get left join to work
I've got 2 MySQL tables: Employees, and Mileage. Mileage records trips for employees and there may be 0 or more Mileage rows for each Employee row. The Mileage table has a trip_date column. For testing purposes, the trip_date column in all rows is set to 2002-03-01. There are currently 15 rows in the Employees table. I want the result of the following select to be 15 rows (1 for each employee, whether or not there are any rows in the Mileage table for that employee). If I take the "where" clause out, I get all 15 rows (including a number of rows where sum(m.miles) = 0 (which is what I want). If I leave the "where" clause in, however, I don't get employees with sum(m.miles) = 0 (even though the "where" clause should include all of the Mileage rows). (Only 3 Employees actually have corresponding rows in the Mileage table, and I only get 3 rows back.) select e.id,lname,sum(m.miles) from employees e left join mileage m on e.id=m.id where month(m.trip_date) = 3 and substring(year(m.trip_date),3,2) = '02' group by e.id; I need to select by month/year from the Mileage table. Any idea how the "select" can be modified to include rows for all of the Employees? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php