Re: Elegant Solution re MTM Relationship?

In article <mwiikCwJx70.GtB@netcom.com> mwiik@netcom.com "Michael Wiik" writes: > We have one table consisting of a single field, I'll call it "HC" > for "header code". Another table contains order codes ("OC"). There > is a many-to-many relationship between order codes and header codes. > The proper header code needs to be determined by the number and value > of non-repeating order codes. I can make a table with a single HC > attribute and a single OC attribute, the combination will be unique. > I could prepare a possibly horrendous sql query to solve this > but am hoping for something more elegant. Heres an idea ! To solve a many to many problem you need another joining table (standard case) So create a table (called mtm in this case) containing all the links required HC OC ABC ABC ABC ABD ABC ABE .... you can then use a select : select hc from mtm m1 where m1.oc in (code1,code2,code3,.....) and not exists (select * from mtm m2 where m2.o not in (code1,code2,code3,....) group by h having count(*)=no_of_codes This works by only selecting rows which have the columns (and no others) and making sure that the count of order headers is the same as that expected. -- Mike Aubury ...---...