On 3/24/06, Michael Lackhoff <[EMAIL PROTECTED]> wrote: > Say, I have two tables: vendors and products. > Now I want a list of all the vendors that have no products yet. > This can be seen from the products table if there are no records with > the vendor_id of the vendor in question. > Is this possible with a 'normal' manager method or with a > relationship or do I have to hand-code the SQL? > > And if I have to hand-code it with get_objects_from_sql -- can you > give me a hint, what would be a good (fast, portable) SQL to do this > kind of query (my SQL isn't that good).
I am not sure how to do this in RDBO (I'm sure John will be able to help you on with that part), but here is the SQL you could use: SELECT vendors.* FROM vendors LEFT JOIN products ON (vendors.id = products.vendor_id) WHERE products.id IS NULL; 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. You can also do it with a subselect: SELECT * FROM vendors WHERE id NOT IN (SELECT vendor_id FROM products); You will have to test which one is more efficient and/or portable as it may differ between different DBs. Cheers, Cees ------------------------------------------------------- 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&kid0944&bid$1720&dat1642 _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
