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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
