Knotty SQL problem
This is a multi-part message in MIME format.
---------------------------------79838617310973053301530195779
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=us-ascii
Can anyone help with the following?:
--
Mike Stenzler 70 Hudson St. 201.798.5784
Mgr. Info. Sys. Hoboken, NJ 201.798.6367 fax
RPCG, Inc. 07030 rpcg@panix.com
All content my opinion only - not RPCG's.
---------------------------------79838617310973053301530195779
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
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
CLIENT FUND FUNDREL
------ ---- -------
cid | sysid fid cid | fid | sysid
------------ ---- -------------------
mike | 22 f1 mike | f1 | 4
| | |
| | |
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;
any ideas?
---------------------------------79838617310973053301530195779--