Hello, I run 3 queries. The first get's everything from a table, the second get's everything from that table that isn't in another table, and the third get's everything from that table that is in another table. Query 2 + Query 3 should equal Query 1 Here are the queries: /*query 1*/ select count(*) from location /*query 2*/ select count(*) from location left join carton on location.facilityid = carton.facilityid and location.loctypeid = carton.loctypeid and location.locationid = carton.locationid where location.loctypeid = 'p' and carton.cartonid is null /*query 3*/ select count(*) from location left join carton on location.facilityid = carton.facilityid and location.loctypeid = carton.loctypeid and location.locationid = carton.locationid where location.loctypeid = 'p' and carton.cartonid is not null my result is: 595413 552299 101712 How is it possible that there is more than the total? Thanks in advance, Ben
In query 2 & 3 you refer in the WHERE clause to the carton table which you LEFT JOINed onto. This effectively turns the OUTER JOIN into an INNER JOIN and may well be the reason why you don't get the results you expect. Move this condition from WHERE to the JOIN.