It seems I forgot to add another criteria
for this. If there are records in AB that have the same Aid but different
Bid one of which is equal to SP.Bid, then the record that has the Bid<>SP.Bid
is returned.
So far, I have obtained, with no small
help from the great people of this list(Allan Marks and others) the following
SQL. Unfortunately, it suffers from what was mentioned earlier. So
I need to add another criteria, specifically to second part of the union
where if one record in AB with a particular Aid is excluded all other records
in AB with Aid should also be excluded.
SELECT * FROM A
WHERE A.Aid NOT IN (
SELECT AB.Aid FROM AB
WHERE AB.Cid = SP.Cid
)
UNION
SELECT * FROM A
WHERE A.Aid IN (
SELECT AB.Aid FROM AB
WHERE AB.Cid = SP.Cid AND AB.Bid != SP.Bid
)
Any ideas?
-Jason "I am coder, not a DBA!"
Kretzer
"Jason Kretzer/STAR
BASE Consulting Inc." <JKretzer@starbaseinc.com>
04/07/2004 11:31 AM
Please respond to
ajug-members@ajug.org
To
ajug-members@ajug.org
cc
Subject
[ajug-members]: inner/outer
join question
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
I am having problems with the syntax, I can't seem to get it to come out
right.
I know people have done these sort of joins before but I am more of a dabbler
in SQL. So, any help would be appreciated.