Re: Knotty SQL problem

Your question is sufficiently confused that I can't answer it positively, and I doubt if anyone can. What I can do is ask a bunch of subsidiary questions, assume some answers, and thereby propose a tentative solution. If your answers are different to my assumptions, you need to explain what you are really doing, and this may enable people to work out what you really want... >From: Mike Stenzler <rpcg@panix.com> >Date: 28 Jul 1995 13:57:32 GMT >X-Informix-List-Id: <news.15806> > >Here's the problem: > >4 tables > > CLIENT FUND FUNDREL CRATE > cid unique fid unique cid sysid > sysid serial fid type > sysid serial commod > rid serial > >one to many relationships between client, fund & fundrel > > CLIENT <----->> FUNDREL <<------> FUND > >one to many relationships between client, fundrel & crate > > CLIENT <----->> CRATE <<-----> FUNDREL > >in other words: a client & fund join together in the fundrel >table to create a unique entity - client:fund. Each client >may have many commission rate records, as may each fundrel. > >Where C=client, F=fundrel as the values in crate.type So, if I understand this correctly, there are constraints such that: CRATE.Type IN ('F', 'C') CRATE.SysID is multi-purpose pseudo-foreign key such that: IF CRATE.Type = 'F' THEN CRATE.SysID REFERENCES FundRel.SysID ELSE -- CRATE.Type = 'C' CRATE.SysID REFERENCES Client.SysID ENDIF This, if I am correct, is a pretty ghastly piece of referential integrity. > CLIENT FUND FUNDREL > ------ ---- ------- > cid | sysid fid cid | fid | sysid > ------------ ---- ------------------- > mike | 22 f1 mike | f1 | 4 > | | | Presumably it is a coincidence that mike has just one fundrel entry at the moment. Likewise, it is a coincidence that f1 is only involved in one fundrel at the moment. > CRATE > ----- > sysid | type | commod | rid > ------------------------------- > 22 | C | ALL | 345 > 4 | F | ALL | 221 > 4 | F | HO | 442 > 4 | F | ADFX | 536 > 22 | C | HO | 122 > 4 | F | W | 123 > 22 | C | CC | 125 > >from the data in crate we can extract the set of rate recs for >the fundrel mike:f1 with the following select: > > select sysid, type, commod, rid > from crate > where > (sysid = 22 and type = C) or (sysid = 4 and type = F) > order by type desc, commod asc;> >this returns: > > 4 | F | W | 123 > 4 | F | HO | 442 > 4 | F | AZFX | 536 > 4 | F | ALL | 221 > 22 | C | ALL | 345 > 22 | C | CC | 125 > 22 | C | HO | 122 > >if a fundrel has a rate of the same commod as the client we want >to overide the client rate by not selecting it. So in the set above >we want to find a way to return the following set: > > 4 | F | W | 123 > 4 | F | HO | 442 > 4 | F | AZFX | 536 > 4 | F | ALL | 221 > 22 | C | CC | 125 > >this ideally should be done in a single select or select with >a subquery, or 2 selects with a join or union. > >the closest I've come is the following self-referential correlated subquery >which doesn't work: > > select c.sysid, c.type, c.commod, c.rid > from crate c > where > (c.sysid = 22 and c.type = C) or (c.sysid = 4 and c.type = F) and > c.rid not in > ( > select t.rid from crate t > where > (t.commod = c.commod) and t.type = C > ) > order by type desc, commod asc; So, what are we trying to look for, exactly? Given your prototype query, it appears that you are given a client sysid AND a fundrel sysid and are trying to sort out something for them both. I think, however, that what you are probably looking for is all the commission rate records for all fundrels associated with a client, or associated with the client but not with any fundrel. Now, what are the input parameters to this query? I think it is just the client id, mike. We can work things out in stages: -- Rates associated with just the client. SELECT R.SysID, R.Type, R.Commod, R.Rid FROM Crate R, Client C WHERE R.Type = 'C' AND C.SysID = R.SysID AND C.Cid = "mike" INTO TEMP Client_Rates; -- Rates associated with the client's FundRels SELECT R.SysID, R.Type, R.Commod, R.Rid FROM Crate R, FundRel F, Client C WHERE R.Type = 'F' AND R.SysID = F.SysID AND F.Cid = C.Cid AND C.Cid = "mike" INTO TEMP FundRel_Rates; This can presumably be simplified to: SELECT R.SysID, R.Type, R.Commod, R.Rid FROM Crate R, FundRel F WHERE R.Type = 'F' AND R.SysID = F.SysID AND F.Cid = "mike" INTO TEMP FundRel_Rates; We now want all the FundRel_Rates, and any Client_Rates where the Commod is not listed amongst the FundRel_Rates, if I understand the requirements correctly... SELECT * FROM FundRel_Rates UNION SELECT * FROM ClientRel_Rates WHERE Commod NOT IN (SELECT DISTINCT Commod FROM FundRel_Rates); Since you want this written as a single statement, you write it as: SELECT R.SysID, R.Type, R.Commod, R.Rid FROM Crate R, FundRel F WHERE R.Type = 'F' AND R.SysID = F.SysID AND F.Cid = "mike" UNION SELECT R.SysID, R.Type, R.Commod, R.Rid FROM Crate R, Client C WHERE R.Type = 'C' AND C.SysID = R.SysID AND C.Cid = "mike" AND R.Commod NOT IN (SELECT DISTINCT S.Commod FROM Crate S, FundRel F WHERE S.Type = 'F' AND S.SysID = F.SysID AND F.Cid = "mike") If this isn't what you actually want, you'd better explain what it is you do want a lot more clearly. Once you do explain what you want clearly, you'll find it is relatively simple to answer the question... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>