Re: SQL: Joining 3 tables!


Jamie Collins wrote:
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.

--

.

Relevant Pages