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

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-08-20 : 10:23:34

I have 3 relationals table as below.
i wanted to list the unique data from these 3 tables.
Can any one help pls, I need these fields
T1F1,T1F2,T2F2,T3F2

there are relation ship with T1F1=T2F1 and T3F1

declare @tbl1 table
(T1F1 varchar(10),
T1F2 varchar(50)
)

INSERT INTO @tbl1
SELECT 'A101' ,'X'

INSERT INTO @tbl1
SELECT 'A102' ,'Y'

INSERT INTO @tbl1
SELECT 'A103' ,'Z'

INSERT INTO @tbl1
SELECT 'A104' ,'Zz'

declare @tbl2 table
(T2F1 varchar(10),
T2F2 varchar(50)
)

INSERT INTO @tbl2
SELECT 'A101' ,'MM'

INSERT INTO @tbl2
SELECT 'A101' ,'MM'

INSERT INTO @tbl2
SELECT 'A101' ,'MM'

INSERT INTO @tbl2
SELECT 'A102' ,'KK'

INSERT INTO @tbl2
SELECT 'A103' ,'LL'

declare @tbl3 table
(T3F1 varchar(10),
T3F2 varchar(50)
)

INSERT INTO @tbl3
SELECT 'A102' ,'DD'

INSERT INTO @tbl3
SELECT 'A102' ,'DD'

INSERT INTO @tbl2
SELECT 'A101' ,'GG'

INSERT INTO @tbl3
SELECT 'A102' ,'DD'

Out Put:
A101 X MM GG
A102 Y KK DD
A103 Z LL NULL
A104 Zz NULL NULL

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-20 : 10:45:01

[code]select A.T1F1
,A.T1F2
,B.T2F2
,C.T3F2
FROM @tbl1 A
LEFT JOIN (SELECT T2F1,MAX(T2F2) AS T2F2 FROM @tbl2 GROUP BY T2F1) B ON A.T1F1 = B.T2F1
LEFT JOIN (SELECT T3F1,MAX(T3F2) AS T3F2 FROM @tbl3 GROUP BY T3F1) C ON A.T1F1 = C.T3F1[/code]or
[code]select DISTINCT A.T1F1
,A.T1F2
,B.T2F2
,C.T3F2
FROM @tbl1 A
LEFT JOIN @tbl2 B ON A.T1F1 = B.T2F1
LEFT JOIN @tbl3 C ON A.T1F1 = C.T3F1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources