LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » How to merge multiple tables in SQL query

How to merge multiple tables in SQL query

Options

Previous Topic · Next Topic

rhst11

Posted: Friday, December 11, 2009 5:11:40 AM

Rank: Newbie

Joined: 12/11/2009
Posts: 1
Points: 3

I'm new to SQL. I have 3 queries created deparately and result like tables below. Now finding a way to merge them together.

Table 1:
ID Name Total_X
1 abc 22
12 bcd 5
6 tfg 11

Table 2:
ID Name Total_y
4 otg 50
1 abc 13
9 eft 21

Table 3:
ID Name Total_Z
10 utt 4
6 tfg 45
5 ddf 19

After the merge of tables should look like this, any help?
Merged table:
ID Name Total_X Total_y Total_z
1 abc 22 13 Null
4 otg Null 50 Null
5 ddf Null Null 19
6 tfg 11 Null 45
9 eft Null 21 Null
10 utt Null Null 4
12 bcd 5 Null Null

pk_bohra

Posted: Tuesday, December 22, 2009 11:01:40 PM

Rank: Newbie

Joined: 5/16/2008
Posts: 2
Points: 6
Where do you live?: Hyderabad

Try This:

Declare @Table1 Table
(Id int,
Name varchar(50),
Total_X int)

Declare @Table2 Table
(Id int,
Name varchar(50),
Total_Y int)

Declare @Table3 Table
(Id int,
Name varchar(50),
Total_Z int)

Insert into @Table1 Values(1,'abc',22)
Insert into @Table1 Values(12,'bcd',5)
Insert into @Table1 Values(6,'tfg',11)

Insert into @Table2 Values(4,'otg',50)
Insert into @Table2 Values(1,'abc',13)
Insert into @Table2 Values(9,'eft',21)

Insert into @Table3 Values(10,'utt',4)
Insert into @Table3 Values(6,'tfg',45)
Insert into @Table3 Values(5,'ddf',19)

Select COALESCE(T1.id,T2.id,T3.id) as Id ,COALESCE(T1.Name,T2.Name,T3.Name) as Name, T1.Total_X, T2.Total_Y, T3.Total_Z from @Table1 T1
full outer join @Table2 T2 on T1.Id = T2.Id
full outer join @Table3 T3 on T1.Id = T3.Id
order by 1

bklr

Posted: Monday, December 28, 2009 1:17:30 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

go through these document it will be useful
http://www.sqlteam.com/article/writing-outer-joins-in-t-sql

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.