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 2005 Forums

Transact-SQL (2005)

JOIN

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-05-07 : 13:54:16

Hello,

I have 4 tables as follows:

Table1: [Id1](PK), ...
Table2: [Id2](PK), [Id1](FK), ...
Table3: [Id3](PK), [Text], ...

Table3Table2: [Id2](PK), [Id3](PK)

I need to do something as follows:

Select records in Table 2 for a given @Id1 (related with Table1)
|
| ---- Select records in Table3Table2 for the records gotten
from Table2
|
| ---- Return Id3 and Text from Table 3 for all
the records taken from Table3Table2

I did the following, but I am getting problems with JOINs:
SELECT
t3.Id3,
t3.Text
FROM dbo.Table3 t3
LEFT JOIN dbo.Table3Table2 t3t2 ON t3.Id3 = t3t2.Id3
LEFT JOIN dbo.Table2 t2 ON t3t2.Id2 = t2.Id2
LEFT JOIN dbo.Table1 t1 ON t2.Id1 = t1.Id1
WHERE t1.Id1 = @Id1
ORDER BY t3.Text ASC

What am I doing wrong?
And should I use Inner Join in this case?
(Not all elements in Table1 have related records in Table2 and so on to Table3Table2 and then Table3)

I get the error:
The multi-part identifier "n.Id2" could not be bound.

Thanks,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-07 : 14:13:40

n.Id2 doesn't exist in the query that you posted. Could you post your actual query so that we can help better?

Inner joins are used when you want only the exact matches in both tables. If you have records that exist in one table and not in the other and want to display these, then you use an outer join.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 14:17:15

[code]
SELECT
t3.Id3,
t3.Text
FROM
(
Select ID1 From Table1 Where ID1 = @ID1
) as T1
Left Outer Join
Table2 T2 On T1.ID1 = T2.ID1
Left Outer Join
Table3Table3 T23 On T23.ID2 = T2.ID2
Left Outer Join Table3
On T23.ID3 = T3.ID3

[/code]

Chirag

http://chirikworld.blogspot.com/

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-05-07 : 14:37:09

Chirag,

It worked fine. Thank You.
Just one question:
In SQL 2005 isn't Left Outer Join the same As Left Join?
I am not sure if this is true and if it is only for SQL 2005.

Thanks,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-07 : 14:40:59

Yes they are the same.

Left join == Left outer join
Join == Inner join
Right join == Right outer join

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources