Re: Help with strange join

On Fri, 4 Feb 2005 09:44:15 +0100, Victor Spång Arthursson
<victor@tosti.dk> wrote:
> God morning, everybody!
>
> I have a problem that I don't seem to be able to solve by my self,
> thats why I kindly ask the list now…
>
> I have a database containing some tables, containing different receipts.
>
> Every receipt have an unknown number of ingredients linked to it, and
> every ingredients name is in a separate table sincce it needs to be
> translated to different languages.
>
> The tables are link according to the following:
>
> receipts <- related_ingredients <- ingredients <- languages
>
> On a listpage I want to list (ten and ten) all receipts that has _all
> the ingredients translated_.
>
> My problem is how to find out that every ingredient is translated.
>
> 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".

Can you send the table structure and the query that does this?  It may
just be a matter of adding a subselect with a HAVING clause, but we
won't know until we have more information.

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org