Jason Kretzer/STAR BASE Consulting Inc. wrote:
Slightly off topic but at least this query is being used in a java app.
I have three tables, A B and AB.
I have two values which are PK in A B respectively, Aid, Bid
I have a value from another table Cid
Cid is also in A
Aid Bid and Cid are in AB and are the PK together
Bid is a specified value referred to below as SP.Bid
Cid is a specified value referred to below as SP.Cid
I would like to get all records in A that meet the following criteria.
records not present in AB whose AB.Cid=SP.Cid
combined with(UNION)
records present in AB whose AB.Aid=A.Aid and AB.Cid=SP.Cid and
AB.Bid<>SP.Bid
Hmmm. I'm more of a dabbler in SQL as well, but you could try this:
select A.(whatever) from A, B, AB where
(AB.Aid <> A.Aid and AB.Cid = SP.Cid) or
(AB.Aid = A.Aid and AB.Cid = SP.Cid and AB.Bid <> SP.Bid)
If that produces the correct results (I'm a little worried about the
"AB.Aid <> A.Aid" not being right), then the rest of the discussion here
applies.
The above query factors down to:
select A.(whatever) from A, B, AB where
AB.Cid = SP.Cid and
(AB.Aid <> A.Aid or (AB.Aid = A.Aid and AB.Bid <> SP.Bid))
Take X as "AB.Aid = A.Aid" and Y as "AB.Bid = SP.Bid". Then the bit in
parentheses above is ~X v (X ^ ~Y), which is equivalent to ~(X ^ Y) --
or, if you prefer, ~X v ~Y. So this factors down to
select A.(whatever) from A, B, AB where
AB.Cid = SP.Cid and not (AB.Aid = A.Aid and AB.Bid = SP.Bid)
or equivalently, if you prefer:
select A.(whatever) from A, B, AB where
AB.Cid = SP.Cid and (AB.Aid <> A.Aid or AB.Bid <> SP.Bid)
Now, since each "SP" ID is specified only once, you could do this:
select A.(whatever) from A, B, AB where
AB.Cid = ? and not (AB.Aid = A.Aid and AB.Bid = ?)
Then you could pass in your specified values (SP.Cid and SP.Bid,
respectively) as parameters to the Statement, which will avoid the need
for escaping quotes and all that good stuff. It may be that the SQL
optimizer in your database of choice will do all the logic
simplifications that I've done manually above; but it might not, so it's
probably a good idea to simplify it down just in case, in order to make
the query faster and more readable for the poor folks who have to
maintain your program. ;)
Hope that helps,
Eric
|