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