On Fri, 4 Feb 2005 17:52:45 +0100, Victor SpÃng Arthursson <victor@xxxxxxxx> 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@xxxxxxxxx GPLS -- PINES Development Database Developer http://open-ils.org ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
