SQL: Joining 3 tables!

[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

 

[Jamie Collins]

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)

I think you may be thinking too hard about your search conditions <g>.

If I've understood correctly, this should work:

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;

Some points of note:

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

As far as I can tell, B.UniqueId is not needed in the join/where/entire

for a given set of PeriodID:s fetches
all records in table A

This confused me. PeriodID is not an column in table A. Did you want a
CROSS JOIN between these tables, maybe?

Jamie.

--

 

[Jamie Collins]

Hans wrote:
If I've understood correctly, this should work

Here's my test code:

Sub TestHans()
Kill "C:\DropMe.mdb"
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

With .ActiveConnection

' Dummy one-row temp table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

.Execute _
"CREATE TABLE Periods (PeriodID INTEGER NOT" & _
" NULL PRIMARY KEY)"
.Execute _
"INSERT INTO Periods (PeriodID) SELECT DT1.PeriodID" & _
" FROM ( SELECT 1 AS PeriodID FROM DropMe" & _
" UNION ALL SELECT 2 FROM DropMe ) AS DT1" & _
" ;"

.Execute _
"CREATE TABLE A (UniqueId CHAR(1) NOT NULL" & _
" PRIMARY KEY);"
.Execute _
"INSERT INTO A (UniqueId) SELECT DT1.UniqueId" & _
" FROM ( SELECT 'W' AS UniqueId FROM DropMe" & _
" UNION ALL SELECT 'X' FROM DropMe UNION" & _
" ALL SELECT 'Y' FROM DropMe UNION ALL SELECT" & _
" 'Z' FROM DropMe ) AS DT1 ;"

.Execute _
"CREATE TABLE B ( A_UniqueId CHAR(1) REFERENCES" & _
" A (UniqueId) ON DELETE NO ACTION ON UPDATE" & _
" NO ACTION, PeriodID INTEGER NOT NULL REFERENCES" & _
" Periods (PeriodID) ON DELETE NO ACTION" & _
" ON UPDATE NO ACTION, PRIMARY KEY (A_UniqueId," & _
" PeriodID));"
.Execute _
"INSERT INTO B (A_UniqueId, PeriodID) SELECT" & _
" DT1.A_UniqueId, PeriodID FROM ( SELECT" & _
" 'Y' AS A_UniqueId, 1 AS PeriodID FROM DropMe" & _
" UNION ALL SELECT 'Y', 2 FROM DropMe UNION" & _
" ALL SELECT 'Z', 1 FROM DropMe UNION ALL" & _
" SELECT 'Z', 2 FROM DropMe ) AS DT1;"

.Execute _
"CREATE TABLE C ( A_UniqueId CHAR(1) REFERENCES" & _
" A (UniqueId) ON DELETE NO ACTION ON UPDATE" & _
" NO ACTION, PeriodID INTEGER NOT NULL REFERENCES" & _
" Periods (PeriodID) ON DELETE NO ACTION" & _
" ON UPDATE NO ACTION, PRIMARY KEY (A_UniqueId," & _
" PeriodID));"
.Execute _
"INSERT INTO C (A_UniqueId, PeriodID) SELECT" & _
" DT1.A_UniqueId, PeriodID FROM ( SELECT" & _
" 'X' AS A_UniqueId, 1 AS PeriodID FROM DropMe" & _
" UNION ALL SELECT 'X', 2 FROM DropMe UNION" & _
" ALL SELECT 'Z', 1 FROM DropMe UNION ALL" & _
" SELECT 'Z', 2 FROM DropMe ) AS DT1;"

.Execute _
"DROP TABLE DropMe;"

Dim rs As Object
Set rs = .Execute( _
"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;")

Dim sCols As String
Dim f As Object
For Each f In rs.Fields
sCols = sCols & f.Name & vbTab & vbTab
Next
sCols = Left$(sCols, Len(sCols) - Len(vbCr & vbTab & vbTab))

MsgBox sCols & vbCr & rs.GetString(2, , vbTab & vbTab, , "(null)")

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[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

 

[Jamie Collins]

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

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>

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

Here follows a revision.

I did wonder (aloud) about the PeriodID values. The name suggests there
is a table missing from your spec. If this really does not appear in
any of your tables (e.g. it actually is a sequence number) then you can
use a auxiliary table of sequential integers, being a standard trick.

Note the derived table, which I've aliased 'CrossJoined':

Sub TestHansTake2()
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe2.mdb;"

With .ActiveConnection

' Dummy one-row temp table for bulk inserts
.Execute _
"CREATE TABLE DropMe (anything INTEGER);"
.Execute _
"INSERT INTO DropMe VALUES (1);"

.Execute _
"CREATE TABLE Periods (PeriodID INTEGER NOT" & _
" NULL PRIMARY KEY)"
.Execute _
"INSERT INTO Periods (PeriodID) SELECT DT1.PeriodID" & _
" FROM ( SELECT 0 AS PeriodID FROM DropMe" & _
" UNION ALL SELECT 1 FROM DropMe ) AS DT1" & _
" ;"

.Execute _
"CREATE TABLE A (UniqueId CHAR(1) NOT NULL" & _
" PRIMARY KEY);"
.Execute _
"INSERT INTO A (UniqueId) SELECT DT1.UniqueId" & _
" FROM ( SELECT '1' AS UniqueId FROM DropMe" & _
" UNION ALL SELECT '2' FROM DropMe) AS DT1 ;"

.Execute _
"CREATE TABLE B ( A_UniqueId CHAR(1) REFERENCES" & _
" A (UniqueId) ON DELETE NO ACTION ON UPDATE" & _
" NO ACTION, PeriodID INTEGER NOT NULL REFERENCES" & _
" Periods (PeriodID) ON DELETE NO ACTION" & _
" ON UPDATE NO ACTION, PRIMARY KEY (A_UniqueId," & _
" PeriodID));"
.Execute _
"INSERT INTO B (A_UniqueId, PeriodID) SELECT" & _
" DT1.A_UniqueId, PeriodID FROM ( SELECT" & _
" '1' AS A_UniqueId, 0 AS PeriodID FROM DropMe" & _
" UNION ALL SELECT '1', 1 FROM DropMe) AS DT1;"

.Execute _
"CREATE TABLE C ( A_UniqueId CHAR(1) REFERENCES" & _
" A (UniqueId) ON DELETE NO ACTION ON UPDATE" & _
" NO ACTION, PeriodID INTEGER NOT NULL REFERENCES" & _
" Periods (PeriodID) ON DELETE NO ACTION" & _
" ON UPDATE NO ACTION, PRIMARY KEY (A_UniqueId," & _
" PeriodID));"
.Execute _
"INSERT INTO C (A_UniqueId, PeriodID) VALUES" & _
" ('1', 0);"

.Execute _
"DROP TABLE DropMe;"

Dim rs As Object
Set rs = .Execute( _
"SELECT CrossJoined.A_UniqueID, B.PeriodID," & _
" C.PeriodID FROM ((SELECT A.UniqueId AS" & _
" A_UniqueId, Periods.PeriodID AS PeriodID" & _
" FROM Periods, A) AS CrossJoined LEFT JOIN" & _
" B ON CrossJoined.A_UniqueID = B.A_UniqueID" & _
" AND CrossJoined.PeriodID = B.PeriodID)" & _
" LEFT JOIN C ON CrossJoined.A_UniqueID =" & _
" C.A_UniqueID AND CrossJoined.PeriodID =" & _
" C.PeriodID;")

Dim sCols As String
Dim f As Object
For Each f In rs.Fields
sCols = sCols & f.Name & vbTab & vbTab
Next
sCols = Left$(sCols, Len(sCols) - Len(vbCr & vbTab & vbTab))

MsgBox sCols & vbCr & rs.GetString(2, , vbTab & vbTab, , "<null>")

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--