On 24 Mar 2006, Cees Hek wrote: > SELECT vendors.* FROM vendors LEFT JOIN products ON (vendors.id = > products.vendor_id) WHERE products.id IS NULL;
Thanks a lot, that works great! > A left join will always grab all matching records from the left table, > even if it doesn't have matching record in the right table, in which > case the results for the right table will have all NULL values. So > doing a left join and checking for a NULL product id should get you > all vendors that do not have a matching row in the products table. I must admit I was almost sure you had a typo in the WHERE clause but now I am beginning to understand how it works. > You can also do it with a subselect: > > SELECT * FROM vendors WHERE id NOT IN (SELECT vendor_id FROM products); Yes this is easier to understand and works too, at least in SQLite, the RDBM I use for development. When the application becomes productive I might want to use MySQL instead and I have heard somewhere that MySQL doesn't support sub-selects. I'll check that later. Thanks for giving both versions, it is good to have an alternative! Cheers, Michael ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
