Joining 4 tables and multiple relationships (1 Viewer)

[bentheimmigrant]

So, I'm trying to learn SQL a little better, to avoid abuse Dlookup, and generally use good practice. Now, the table setup probably isn't quite ideal, but it is what it is at this stage, and there's no going back.

I'm trying to figure out how to join 4 tables, but with two of the tables being part of the Join to the last one. The structure is as follows:

tblA, with key ID, and field B_ID
tblB with non-unique field B_ID, non-unique field B_Position (the position is unique for each B_ID), and field C_ID
tblC with key ID, and field C_Position (the position is not unique, but it is for
tblD, with fields A_ID and D_Position (which should match C_position. This is where the structure probably isn't ideal)

I'm hoping build a string to join all 4 tables, based on the criteria of tblA.ID = vbID (an integer in the routine)

I had a go (well, a few more than one), looking generally like this:

SQLstr = "SELECT * FROM ((tblA" & _
    "LEFT JOIN tblB ON tblA.B_ID = tblB.B_ID) " & _
    "INNER JOIN tblC ON tblsB.C_ID = tblC.ID) " & _
    "LEFT JOIN (tblD ON tblA.ID = tblD.A_ID " & _
    "AND tblC.C_Position = tbl_D.D_position) " & _
    "WHERE tblA.ID = " & vbID

I end up with error 3296, "JOIN expression not supported." Which I think is because of the AND? But I've tried it without the AND, just to see what would happen, and it still has a JOIN error. If I use INNER JOIN for all of them, I get no results (but there definitely should be).

Can I ORDER BY the two position fields, to get rid of that AND?

Now, to the untrained eye, it may appear that I have half a clue. But to everyone else, it should be apparent that I do not.

 

[static]

You haven't told it where to get the fields from.
Only that you want all of the fields and how the tables are joined.

This is what you have now restructured a bit

SELECT * FROM 

	(
		(tblA LEFT JOIN tblB ON tblA.B_ID = tblB.B_ID) 
		INNER JOIN tblC ON tblsB.C_ID = tblC.ID
	)
	LEFT JOIN (
		tblD ON tblA.ID = tblD.A_ID AND tblC.C_Position = tbl_D.D_position
		) 
WHERE tblA.ID = " & vbID

You probably want this

SELECT tblA.*,tblB.*,tblC.* FROM tblA

	(
		(tblA LEFT JOIN tblB ON tblA.B_ID = tblB.B_ID) 
		INNER JOIN tblC ON tblsB.C_ID = tblC.ID
	)
	LEFT JOIN (
		tblD ON tblA.ID = tblD.A_ID AND tblC.C_Position = tbl_D.D_position
		) 
WHERE tblA.ID = " & vbID

 

[TJPoorman]

SELECT tblA.*,tblB.*,tblC.* FROM [COLOR="Red"]tblA[/COLOR]

	(
		(tblA LEFT JOIN tblB ON tblA.B_ID = tblB.B_ID) 
		INNER JOIN tblC ON tblsB.C_ID = tblC.ID
	)
	LEFT JOIN [COLOR="Red"]([/COLOR]
		tblD ON tblA.ID = tblD.A_ID AND tblC.C_Position = tbl_D.D_position
		[COLOR="red"])[/COLOR] 
WHERE tblA.ID = " & vbID

You're going to throw an error with that SQL. Lose the tblA in red and possibly the parenthesis around tblD join.

 

[bentheimmigrant]

Thanks, both.

Am I correct with the LEFT and INNER joins? tblA is a single record, the others are multiple.