join query - 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

General SQL Server Forums

New to SQL Server Programming

join query

Author

Topic

munishk
Starting Member

3 Posts

Posted - 2008-01-04 : 11:36:05

I have tab1 as:

Col1 Col2 Col3
1 2 Null

Then the Master as:

Col Name

1 One
2 Two
3 Three

I want the output as

Col1 Col2 Col3
One Two Null

Any help? I have been struggling since yesterday.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-04 : 11:46:07

[code]SELECT t2.Name AS 'Col1',
t3.Name AS 'Col2',
t4.Name AS 'Col3'
FROM tab1 t1
OUTER APPLY ( SELECT Name
FROM Master
WHERE Col=t1.Col1)t2
OUTER APPLY ( SELECT Name
FROM Master
WHERE Col=t1.Col2)t3
OUTER APPLY ( SELECT Name
FROM Master
WHERE Col=t1.Col3)t4[/code]

munishk
Starting Member

3 Posts

Posted - 2008-01-05 : 04:31:17

I think APPLY operator works only in SQL Server 2005.
I am working on SQL Server 2000. Forgot to mention that in my previous post.
Can you suggest a solution for SQL 2000?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-05 : 06:59:21

Try this then :-

SELECT t2.[Name] AS 'Col1',
       t3.[Name] AS 'Col2',
       t4.[Name] AS 'Col3'
FROM tab1 t1
LEFT OUTER JOIN ( SELECT Col,[Name] 
              FROM Master )t2
ON t2.Col=t1.Col1
LEFT OUTER JOIN ( SELECT Col,[Name] 
              FROM Master )t3
ON t3.Col=t1.Col2
LEFT OUTER JOIN ( SELECT Col,[Name] 
              FROM Master )t4
ON t4.Col=t1.Col3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources