Re: Help with strange join - Mailing list pgsql-general

On Fri, 4 Feb 2005 17:52:45 +0100, Victor Spång Arthursson
<victor@tosti.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@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org