Mutiple table JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Mutiple table JOIN

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-06 : 07:44:15

David Gordon writes "I'm having difficulty joining four tables, whereby the keyfield of the first table (i.e. paramater 1) is needed to link through to the last table to produce a list of results.

As an added complication the second table join has multiple selection entries and this needs to be filtered by a specific value (i.e paramater2). Here's a breakdown of what I'm trying to achieve;

Select * FROM table1
JOIN table1 ON table2.field1 = table1.field1
JOIN table2 ON table3.field1 = table2.field2
JOIN table3 ON table4.field1 = table3.field2
JOIN table4 ON table3.field1 = 'parameter 1'
WHERE table2.field3 = 'paramater 2'

I'm not sure if it would be best to use embedded SELECT statements but as my SQL knowledge is limited I would be gratful for any guidance in doing this.

Kind Regards
Dave Gordon"

Kristen
Test

22859 Posts

Posted - 2005-07-06 : 13:23:15

You are forward joining table1 (second instance) to table2 - which is not in scope at that point; I don't think this is permitted. Likewise table2 forward joined to table3, and table3 to table4.

As you have two instances of table1 you need to alias each one so that other references to them as explicit.

You are joining table4 based on a condition on table3 - which will work, I suppose, but will cause a cartesian join.

I would put the WHERE clause for table2 in the JOIN for table2 - no particular reason, just that I think its likely to be more efficient.

Kristen

raclede
Posting Yak Master

180 Posts

Posted - 2005-07-06 : 23:43:18

Select * FROM table1 st
JOIN table1 t1 ON t1.field1 = st.field1
JOIN table2 t2 ON st.field1 = t2.field2 AND t2.field3 = 'paramater 2'
JOIN table3 t3 ON st.field1 = t3.field2 AND t3.field1 = 'parameter 1'
JOIN table4 t4 ON st.field1 = t4.field2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources