SQL: Joining 3 tables!

H

Hans

Hi!

I have three tables A, B and C (Access 2000). There is a relation that table
A may have 0 or more of records in table B and C.

Table A:
* UniqueId (primary key, string)
* Some other fields that holds the interesting information but not needed in
the join/where syntax

Table B:
* UniqueId (primary key, string)
* A_UniqueId (key to a table A record)
* PeriodID (integer)
* Some other fields that holds the interesting information but not needed in
the join/where syntax

Table C:
* UniqueId (primary key, string)
* A_UniqueId (key to a table A record)
* PeriodID (integer)
* Some other fields that holds the interesting information but not needed in
the join/where syntax

Table A may have zero or more related records in table B and C with
different PeriodID (The combination of A_UniqueId and PeriodID is unique in
table B and C). I want a query that for a given set of PeriodID:s fetches
all records in table A and if there exist records in Table B and C with
PeriodID:s in the set they should also be returned. If there exist a record
in both table B and C with the same A_UniqueId and PeriodID I want these to
come in the same row in the resultset.

My first attempt was to just use two left joins like (peridodId= 1 or 2 in
this example)

SELECT A.UniqueId, B.PeriodID , C.PeriodID FROM A (left join B on
A.UniqueId=B.A_UniqueId) LEFT JOIN C on A.UniqueId = C.A_UniqueId
WHERE
(B.PeriodID IN (1,2) OR B.PeriodID is Null) AND
(C.PeriodID IN (1,2) OR C.PeriodID is Null) AND
(B.PeriodID = C.PeriodID OR B.PeriodID is Null OR C.PeriodID is Null)

In the result I want always data from table A. If records exist in table B
with correct PeriodID but not in table C, data from table B should also be
returned and null values for the columns from table C (and vice versa if
record exist in table C but not in table B). If there exist records in both
table B and C and they have the same periodID I want these on the same row.
If there exist records in table B and C with the same A_UniqueId but with
different values in PeriodID they should be returned as two records. The
query should never return data where one record in the resultset have
B.PeriodID=1 and C.PeriodID=2 in the example above.

A.UniqueId B.PeriodId C.PeriodId
Some_UniqueId <null> <null> //there exist neither a
table B or table C record
Some_UniqueId <null> 1 //exist a C record
but not a B record
Some_UniqueId <null> 2 //exist a C record
but not a B record
Some_UniqueId 1 <null> //exist a B record
but not a C record
Some_UniqueId 2 <null> //exist a B record
but not a C record
Some_UniqueId 1 1 //exist a B and C
record with the same A_UniqueId and PeriodID combination
Some_UniqueId 2 2 //exist a B and C
record with the same A_UniqueId and PeriodID combination

Not legal answers
A.UniqueId B.PeriodId C.PeriodId
Some_UniqueId 1 2
Some_UniqueId 2 1

If there exist a record in table B with a specific A_UniqueId and periodId 1
and one record in table C with the same A_uniqueId but with period 2 there
should be two records returned
A.UniqueId B.PeriodId C.PeriodId
Some_UniqueId 1 <null>
Some_UniqueId <null> 2

In my attempt query above I lose some records due to null values (I miss the
rows where there only exist records in one of the tables B and C).

Regards
/Hans
 
H

Hans

Hi Jamie!

Thank you for your efforts! Unfortunately it does not really solve my
problem. Let's say we have the following rows in the three tables.

Table A:
UniqueID
1
2

Table B (you are right in that uniqueId is not needed here but we have one
anyway. It is easier for applications to have a one field key so we don't
have to keep track of multicolumn keys etc).
UniqueId A_UniqueId PeriodId
1 1 0
2 1 1

Table C
UniqueId A_UniqueId PeriodId
1 1 0

The query should result in these three rows
A.UniqueID B.PeriodID C.PeriodID
1 0 0
1 1 <null>
2 <null> <null>

If I run a query like (which lacks condition on the periodID which I must
have in the end but I guess that is just to add in the where clause)

SELECT A.UniqueId, B.PeriodID, C.PeriodID FROM (A left join B on
A.UniqueId = B.A_UniqueId) LEFT JOIN C on A.UniqueId = C.A_UniqueId
WHERE B.PeriodID = C.PeriodID
OR B.PeriodID IS NULL
OR C.PeriodID IS NULL;

will only return two rows.
A.UniqueID B.PeriodID C.PeriodID
1 0 0
2 <null> <null>

The criteria of B.PeriodID = C.PeriodID must be there in some way since I
don't want a resultset where the periodID:s of the table B and C are not
null but different (i.e if both periodId have a value<>null they must be the
same on the same row in the resultset).

I have not used cross joins in access (is it supported?).


Regards
/Hans
 
Top