SQLTeam.com | Weblogs | Forums

Joining 4 tables


My problem is complex. In order to find the information in the AgreementTable (key = agreement_id) for a gridview, I need to first find the owner's ID by their name in their OwnerTable (owners_id and owner_name), then with the owners_id I connect to their OwnerTractTable (has the owners_id and tract_id), this connects to their tract table TractTable (tract_id and agreement_id) and you end up with the agreement_id used to select all the records from the AgreementTable.

So how to construct the joins?



Something like shown below. If you have one to many releationships, you may end up getting more than one row from AgreementTable for a given owner_name or owner_id.

	ot.owner_name, at.col1, at.col2 -- etc
	AgreementTable at
	INNER JOIN TractTable tt ON
		tt.agreement_id = at.agreement_id
	INNER JOIN OwnerTractTable owt ON
		owt.tract_id = tt.tract_id
	INNER JOIN OwnerTable ot ON
		ot.owners_id = owt.owners_id
	ot.owner_name = 'Smith';


Personally I would sequence the tables in the FROM / JOINs in reverse order. It doesn;t make any difference to SQL, but I think it is easier to read if you start with ot.owner_name = 'Smith' and the OwnerTable is at the top (i.e. FROM OwnerTable) and then read down OwnerTractTable, TractTable and finally getting to AgreementTable


Good job! After just a bit of tweeking the joins worked (it was more of me having the id_xxx instead of xxx_id to get it to work.

Thanks James!


agree completely



Hehe, I agree too :smile: