Restating your problem as I understand it:
Table A
PK = Aid
Data = ...
Table B
PK = Bid
Data = ...
Table AB
PK = Aid, Bid, Cid
FK1 = A.Aid
FK2 = B.Bid
Data = ...
Criteria Set 1
1) Records from A
2) Records not part of a special subset of AB
Criteria Set 2
1) Records from AB
2) Records part of a special subset of AB
3) Records present in A
4) Records not present in AB
To achieve the requirement that I outlined, you need the following query:
SELECT
...
FROM
A
WHERE
NOT EXISTS (
SELECT
1
FROM
AB
WHERE
AB.Aid = A.Aid
AND AB.Cid = ?
)
UNION
SELECT
...
FROM
AB
WHERE
AB.Cid = ?
AND AB.Aid = A.Aid
AND NOT EXISTS (
SELECT
1
FROM
B
WHERE
B.Bid = AB.Bid
)
If I misunderstood your requirement, just let me know, and I'll create
another query.
Happy data gathering,
Kevin
-----Original Message-----
From: Jason Kretzer/STAR BASE Consulting Inc.
[mailto:JKretzer@xxxxxxxxxxxxxxx]
Sent: Wednesday, April 07, 2004 11:31 AM
To: users@xxxxxxxxxx
Subject: [cinjug-users] SQL syntax 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.
Thanks,
-Jason
|