Re: Help with strange join

On Fri, 4 Feb 2005 17:52:45 +0100, Victor Spång Arthursson
<victor(at)tosti(dot)dk> wrote:
>
> 2005-02-04 kl. 13.00 skrev Mike Rylander:
>
> > 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.
>
> Sure - coming up!
>
> First table is the main receipt table:
>
> tostipippitest=# select nummer, opskriftsnavn as receiptname from
> opskrifter as receipts;
> nummer | receiptname
> --------+--------------------
> 12345 | 882120001093591418
> 121222 | 534886001105088842
> 33233 | 217710001096269634
> (3 rows)
>
> The id in this table is to be found in the related_ingredients-table:
>
> tostipippitest=# select id, ingrediens, maengde as amount, opskrift
> from opskrifter_content as related_ingredients where opskrift = 12345;
> id | ingrediens | amount | opskrift
> ----+------------+--------+--------
> 8 | i21 | 2 | 12345
> 9 | i18 | 7 | 12345
> 11 | i24 | | 12345
> 4 | i17 | 2 | 12345
> 3 | i14 | 1 | 12345
> (5 rows)
>
> Then there is the ingredients-table that the above relates to:
>
> tostipippitest=# select distinct betegnelse, opskrifter_ingredienser.id
> from opskrifter_content join opskrifter_ingredienser on ingrediens =
> opskrifter_ingredienser.id where opskrifter_content.opskrift = '12345';
> betegnelse | id
> --------------------+-----
> 33794001087300778 | i24
> 135860001084976781 | i14
> 366841001086602763 | i17
> 377948001087300210 | i21
> 722896001087299185 | i18
> (5 rows)
>
> And last there is the language-table:
>
> tostipippitest=# select * from languages limit 10 offset 0;
> relid | text | lang
> --------------------+---------------------------------------------
> +------
> 541388001083147128 | Lagervare Indasia + ID Andet 0 | DK
> 542973001083147128 | Specialbl. lagervare ID Pulver 100 | DK
> 544538001083147128 | Specialbl. lagervare ID Flydende 500 | DK
> 546152001083147128 | Specialbl. lagervare ID Andet 0 | DK
> 547733001083147128 | Specialbl. til SM lagervare ID Pulver 100 | DK
> 549545001083147128 | Specialbl. til SM lagervare ID Flydende 500 | DE
> 551072001083147128 | Specialbl. til SM lagervare ID Andet 0 | DK
> 552622001083147128 | Specialbl.kunder - ej lager ID Pulver 100 | DK
> 554156001083147128 | Specialbl.kunder - ej lager ID Flydende 500 | DK
> 555959001083147128 | Specialbl.kunder - ej lager ID Andet 0 | DK
> (10 rows)
>
> As you can see, there is for exampe only one entry with german
> language, "DE". When joining the ingredients on this table, the result
> can be max one entry. That is a result, but since it is less than the
> number of entrys in the table related_ingredients, I want all of the
> query to fail, thus not returning the actual receipt as a receipt
> totally translated in my dummy-SQL looking something like "SELECT *
> from reciepts JOIN (this strange sql that returns only the numbers of
> the receipts that are totally translated) on receiptnumber =
> othertable.receipt"…

How about:

SELECT r.* FROM opskrifter r JOIN opskrifter_content c ON (r.nummer =
c.opskrift) JOIN opskrifter_ingredienser i ON (c.ingrediens = i.id)
WHERE EXISTS ( SELECT l.relid FROM languages l WHERE l.relid =
i.betegnelse GROUP BY l.relid HAVING COUNT(DISINCT l.lang) = 1);

I tested the subselect but not the entire thing. You could turn that
subselect into a join instead of an EXISTS, though I'm not sure which
will be faster:

SELECT r.* FROM opskrifter r JOIN opskrifter_content c ON (r.nummer =
c.opskrift) JOIN opskrifter_ingredienser i ON (c.ingrediens = i.id)
JOIN ( SELECT l.relid FROM languages l GROUP BY l.relid HAVING
COUNT(DISINCT l.lang) = 1) s ON (s.relid = i.betegnelse);

Hope that helps!

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org