The tables are link according to the following:
receipts <- related_ingredients <- ingredients <- languages
If I just do JOINs, I will not be able to find out if only one or all of the ingredients are translated. What I need is something that, for example, returns the receiptnumber if, and only if, the number of translated ingredients that is returned (after joining ingredients with languages) is the same as the number of entrys in the table "related_ingredients".
Something like this could work (column names and values made up while going on):
SELECT * FROM (receipts INNER JOIN related_ingredients ON (receipt_id)) r OUTER JOIN (ingredients INNER JOIN languages ON (ingredient_id)) i WHERE i.language_id = 1 AND i.name IS NOT NULL
The idea is that the outer join allows receipts with untranslated ingredients to end up in the results as NULL values, so that you can check for them.
I suppose a HAVING clause would also work, but AFAIK you need to GROUP BY to be able to do that...
All of this is untested of course, so I may have made mistakes.
-- Alban Hertroys MAG Productions
T: +31(0)53 4346874 F: +31(0)53 4346876 E: alban@xxxxxxxxxxxxxxxxx W: http://www.magproductions.nl
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
