Extract data from multiple tables into one table

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

Extract data from multiple tables into one table

Author

Topic

pankaj2910
Starting Member

31 Posts

Posted - 2014-04-28 : 14:12:53

Let us suppose I've 3 tables in which 2 columns data are same in all 3 tables & rest fields are distinct
Table 1 :
ID1 ID2 Gender Code towncode
1 12 1 234 1234
2 13 2 543 5463
89 187 1 125 N21Q

Table 2 :
ID1 ID2 Relation Name DOB
1 12 13 XYZ 21/01/1967
1 12 1 QAS 01/10/1987
1 12 2 NHS 09/12/2001
2 13 1 NHG 10/01/1987
2 13 2 GHS 16/12/1999
89 187 2 KJA 31/03/2000
89 187 1 KLA 09/11/1986
89 187 5 KOP 19/12/2001

Table 3 :
ID1 ID2 Period Date
1 12 1 01/01/2011
2 13 1 01/01/2011
89 187 2 01/01/2011

I want result as :
ID1 ID2 Gender Code towncode Relation Name DOB Period Date
1 12 1 234 1234 13 XYZ 21/01/1967 1 01/01/2011
1 12 1 234 1234 1 QAS 01/10/1987 1 01/01/2011
1 12 1 234 1234 2 NHS 09/12/2001 1 01/01/2011
2 13 2 543 5463 1 NHG 10/01/1987 1 01/01/2011
2 13 2 543 5463 2 GHS 16/12/1999 1 01/01/2011
89 187 1 125 N21Q 2 KJA 31/03/2000 2 01/01/2011
89 187 1 125 N21Q 1 KLA 09/11/1986 2 01/01/2011
89 187 1 125 N21Q 5 KOP 19/12/2001 2 01/01/2011

Please help me to get this result. May be some duplicate entry of same record is available in Table 1.

pankajrocks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-28 : 14:19:08

Something like (untested):


select t1.ID1, t1.ID2, t1.Gender, t1.Code, t1.towncode, t2.Relation, t2.Name, t2.DOB, t3.Period, t3.Date
From t1
join t2 on t1.ID1 = t2.ID1 and t1.ID2 = t2.ID2
Join t3 on t1.ID1 = t3.ID1 and t1.ID2 = t3.ID2

pankaj2910
Starting Member

31 Posts

Posted - 2014-04-30 : 05:58:57

Result not extracted & per I want.

Please help any one

pankajrocks

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-30 : 07:49:21

Could you explain where Gbritton solution went wrong
It's worked fine for me...


SELECT		    T1.ID1
		  , T1.ID2
		  , T1.Gender
		  , T1.Code
		  , T1.TownCode
		  , T2.Relation
		  , T2.Name
		  , T2.DOB
		  , T3.Period
		  , T3.Date
FROM		TableA AS T1
INNER  JOIN     TableB AS T2
ON			T1.ID1 = T2.ID1 AND T1.ID2 = T2.ID2
INNER JOIN      TableC AS T3
ON			T1.ID1 =T3.ID1  AND T1.ID2 = T3.ID2

--REsult :
ID1	ID2	Gender	Code	TownCode	Relation	Name	DOB	Period	Date
1	12	1	234	1234	13	XYZ	21/01/1967	1	01/01/2011
1	12	1	234	1234	1	QAS	01/10/1987	1	01/01/2011
1	12	1	234	1234	2	NHS	09/12/2001	1	01/01/2011
2	13	2	543	5463	1	NHG	10/01/1987	1	01/01/2011
2	13	2	543	5463	2	GHS	16/12/1999	1	01/01/2011
89	187	1	125	N21Q	2	KJA	31/03/2000	2	01/01/2011
89	187	1	125	N21Q	1	KLA	09/11/1986	2	01/01/2011
89	187	1	125	N21Q	5	KOP	19/12/2001	2	01/01/2011

Highlight the part where your result does not match..

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources