Re: Help with strange join

Victor Spång Arthursson wrote:
> 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(at)magproductions(dot)nl
W: http://www.magproductions.nl