Re: Help with strange join

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"…

Thanks in advance,

Victor Spång Arthursson
Denmark / Sweden