Canman2005 Posted January 8, 2009 Share Posted January 8, 2009 Hi all I have the following statement SELECT * FROM customers m INNER JOIN orders o ON m.id = o.customers_id this gets all `customers` which have a row in the `orders` table How can I alter this to get all `customers` which don't have a row existing in the `orders` table? Thanks in advance Link to comment https://forums.phpfreaks.com/topic/139945-getting-rows-that-dont-exist/ Share on other sites More sharing options...
sKunKbad Posted January 8, 2009 Share Posted January 8, 2009 The following is not real code (obviously), but is how I'd try to make it work: SELECT customer_id from orders // php that puts all query results in an array SELECT id from customers WHERE id NOT (in the array of customer who have orders) Link to comment https://forums.phpfreaks.com/topic/139945-getting-rows-that-dont-exist/#findComment-732209 Share on other sites More sharing options...
Canman2005 Posted January 8, 2009 Author Share Posted January 8, 2009 Thanks, I tried SELECT * FROM customers WHERE id NOT (SELECT m.id FROM customers m INNER JOIN orders o ON m.id = o.customers_id) ORDER BY datetime DESC But that doesnt seem to do anything and produces an error Link to comment https://forums.phpfreaks.com/topic/139945-getting-rows-that-dont-exist/#findComment-732214 Share on other sites More sharing options...
sasa Posted January 8, 2009 Share Posted January 8, 2009 SELECT * FROM (customers m LEFT JOIN orders o ON m.id = o.customers_id) WHERE o.id IS NULL Link to comment https://forums.phpfreaks.com/topic/139945-getting-rows-that-dont-exist/#findComment-732227 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.