SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 JOIN		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shapper
Constraint Violating Yak Guru

443 Posts
Posted - 05/07/2007 :  13:54:16  Show Profile  Reply with Quote
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

USA
35007 Posts
Posted - 05/07/2007 :  14:13:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts
Posted - 05/07/2007 :  14:17:15  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote 

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


Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

shapper
Constraint Violating Yak Guru

443 Posts
Posted - 05/07/2007 :  14:37:09  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 05/07/2007 :  14:40:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000