Joining 4 tables

Transact-SQL
1

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?

Thanks!

2

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.

SELECT
	ot.owner_name, at.col1, at.col2 -- etc
FROM
	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
WHERE
	ot.owner_name = 'Smith';
3

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

1 Like
4

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!

5

agree completely

+1

1 Like
6

Hehe, I agree too :smile:

1 Like