Re: Help with strange join - Mailing list pgsql-general
From | Victor Spång Arthursson |
---|---|
Subject | Re: Help with strange join |
Date | |
Msg-id | 05944f3b6e517ae1448affde26cb3510@tosti.dk Whole thread Raw |
In response to | Re: Help with strange join (Mike Rylander <mrylander@gmail.com>) |
Responses |
Re: Help with strange join
|
List | pgsql-general |
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
pgsql-general by date: