combining 4 tables

[gennesis]

Hi a have 4 tables

Table A
ID | Data 1
---------------
1 | A

Table B
ID | Data 2
-----------------
1 | B
1 | C

Table C
ID | Data 3
----------------
1 | D
1 | E
1 | F

Table D
ID | Data 4
----------------
1 | G
1 | H

I want to have this result

Table Result
ID | Data 1 | Data 2 | Data 3 | Data 4
-----------------------------------------------------------------------
1 | A | B | D | G
| | C | E | H
| | | F |

Pls help.....

[adam_k]

I can only think of cursor for this one.
It might not be the best way, but you don't want a join or acartesian product.
Read here for cursors: http://msdn.microsoft.com/en-us/library/ms180169.aspx

[gennesis]

I kept on trying several combination of joins but its seems a could not achieve my desired output. Ive read the article on the link but it seems so complicated. is there any simple way to do this???

[waqasaslammmeo]

hello !
i have not use this query at my end ,please let me know if you have prob .

select ID,Data1 ,Data2,Data3,Data4
from (
select tA.id,tA.Data1 as Data1,"" as Data2,"" as Data3,"" as Data4
from TableA tA
group by tA.ID
union all
select tB.id,"" as Data1,tB.Data2 as Data2,"" as Data3,"" as Data4
from TableB tB
group by tB.ID
union all
select tC.id,"" as Data1,"" as Data2,tC.Data3 as Data3,"" as Data4"
from TableC tC
group by tC.ID
union all
select tD.id,"" as Data1,"" as Data2,"" as Data3,tD.Data4 as Data4
from TableD tD
group by tD.ID
) as n 
group by id,Data1,Data2,Data3,Data4
order by ID,Data1,Data2,Data3,Data4

hope this will helps you
Regards

M.Waqas Aslam

[gennesis]

Thanks for the reply. ive tried your query and the result is like this:

ID | Data1 | Data2 | Data3 | Data4
=================================================
1 | | | | G
-------------------------------------------------
1 | | | D |
-------------------------------------------------
1 | | B | |
-------------------------------------------------
1 | A | | |

[adam_k]

hm, here's a theory without cursor:

declare @data1 table (id int identity(1,1), ID2 varchar(10), Data1 varchar(10))
declare @data2 table (id int identity(1,1), ID2 varchar(10), Data2 varchar(10))
declare @data3 table (id int identity(1,1), ID2 varchar(10), Data3 varchar(10))
declare @data4 table (id int identity(1,1), ID2 varchar(10), Data4 varchar(10))

insert into @data1 (id2,data1) 
select id, data1 from tablea 
where id = 1 
insert into @data2 (id2,data2) 
select id, data2 from tableb 
where id = 1 
insert into @data3 (id2,data3) 
select id, data3 from tablec 
where id = 1 
insert into @data4 (id2,data4) 
select id, data4 from tabled 
where id = 1 

select coalesce(@data1.id2,@data2.id2,@data3.id2,@data4.id2),data1,data2,data3,data4 
from @data1 full join @data2 on @data1.id = @data2.id 
full join @data3 on @data1.id = @data3.id 
full join @data4 on @data1.id = @data4.id

I haven't tried this, so it might not work or contain errors. If you are having problems with it I'll have SQL available tomorrow.

[gennesis]

thanks adam. i will try it. but if you have any sql statement that much simplier it is a great help.

[gennesis]

select ID,Data1 ,Data2,Data3,Data4
from (
select tA.id,tA.Data1 as Data1,"" as Data2,"" as Data3,"" as Data4
from TableA tA
group by tA.ID
union all
select tB.id,"" as Data1,tB.Data2 as Data2,"" as Data3,"" as Data4
from TableB tB
group by tB.ID
union all
select tC.id,"" as Data1,"" as Data2,tC.Data3 as Data3,"" as Data4"
from TableC tC
group by tC.ID
union all
select tD.id,"" as Data1,"" as Data2,"" as Data3,tD.Data4 as Data4
from TableD tD
group by tD.ID
) as n
group by id,Data1,Data2,Data3,Data4
order by ID,Data1,Data2,Data3,Data4

I revise the statement above by removing the group by on every select statement and this is the result.

ID | Data1 | Data2 | Data3 | Data4
=================================================
1 | | | | H
-------------------------------------------------
1 | | | | G
-------------------------------------------------
1 | | | D |
-------------------------------------------------
1 | | | E |
-------------------------------------------------
1 | | | F |
-------------------------------------------------
1 | | B | |
-------------------------------------------------
1 | | C | |
-------------------------------------------------
1 | A | | |

I do not know if there is any function that we could move this thing, somewhat like cursor positioning as adam suggested.

[waqasaslammmeo]

i think you have to use another select statement to get records according to your required format ,

select ID,Data1 ,Data2,Data3,Data4
from(
select ID,Data1 ,Data2,Data3,Data4
from (
select tA.id,tA.Data1 as Data1,"" as Data2,"" as Data3,"" as Data4
from TableA tA
group by tA.ID
union all
select tB.id,"" as Data1,tB.Data2 as Data2,"" as Data3,"" as Data4
from TableB tB
group by tB.ID
union all
select tC.id,"" as Data1,"" as Data2,tC.Data3 as Data3,"" as Data4"
from TableC tC
group by tC.ID
union all
select tD.id,"" as Data1,"" as Data2,"" as Data3,tD.Data4 as Data4
from TableD tD
group by tD.ID
) as n 
group by id,Data1,Data2,Data3,Data4
) as main
group by id,Data1,Data2,Data3,Data4
order by id,Data1,Data2,Data3,Data4

I think this time it will provide the same result you want ,
Regards

M.Waqas Aslam

[adam_k]

thanks adam. i will try it. but if you have any sql statement that much simplier it is a great help.

It's not that complicated. The alternative is:

select a.id, data1,data2,data3,data4
from (select id, data1, row_number over(order by data1) as row from tablea ) a 
outer join (select id, data2, row_number over (order by data2) as row from tableb) b 
on a.row = b.row
outer join (select id, data3, row_number over (order by data3) as row from tablec) c 
on a.row = c.row and b.row = c.row 
outer join (select id, data4, row_number over (order by data4) as row from table d)  d 
on a.row = d.row and b.row = d.row and c.row = d.row

Again it's not tested. The theory behind both queries is to create a field to join that will provide you the layout you've requested

[BitBlt]

Whew! This was a fun one.

@adam_k, I tried your solution (after minor correction) and (alas) it didn't work. Part of the problem is that there are different row counts in each table, and so when you specify that each must join to the others, it doesn't have a row number to join on when there aren't any rows returned on the other sub selects.

I did manage to get it to work properly with a cursor. Here's what I came up with:

-- Prepare a temp table
create table #temp(id int, [Data 1] char(1) null, [Data 2] char(1) null, [Data 3] char(1) null, [Data 4] char(1) null)
-- Declare local variables
declare @aId int , @aVal char(1), @bId int , @bVal char(1), @cId int , @cVal char(1), @dId int , @dVal char(1) 
-- Declare cursors
declare csrA  cursor for select * from dbo.a	
declare csrB  cursor for select * from dbo.b
declare csrC  cursor for select * from dbo.c
declare csrD  cursor for select * from dbo.d
-- Open the cursors
open csrA
open csrB
open csrC
open csrD
-- Get the first row from each cursor
fetch next from csrA into @aId, @aVal
fetch next from csrB into @bId, @bVal
fetch next from csrC into @cId, @cVal
fetch next from csrD into @dId, @dVal
-- Start looping 
while 1 = 1
begin
	-- Check to see if there's any data...if not, get out of the loop
	if @aId is null and @bId is null and @cId is null and @dId is null
		break
	-- Insert a row
	insert into #temp (id, [Data 1], [Data 2], [Data 3], [Data 4])
	values (@aId, @aVal, @bVal, @cVal, @dVal)
	-- Get the next row of each cursor.  
	-- @@FETCH_STATUS <> 0 means no row, so null out the local variables
	fetch next from csrA into @aId, @aVal
	if @@FETCH_STATUS <> 0
		select @aId = null, @aVal = NULL
	fetch next from csrB into @bId, @bVal
	if @@FETCH_STATUS <> 0
		select @bId = null, @bVal = NULL
	fetch next from csrC into @cId, @cVal
	if @@FETCH_STATUS <> 0
		select @cId = null, @cVal = NULL
	fetch next from csrD into @dId, @dVal
	if @@FETCH_STATUS <> 0
		select @dId = null, @dVal = NULL
end
-- Close and clean up! 
close csrA
deallocate csrA
close csrB
deallocate csrB
close csrC
deallocate csrC
close csrD
deallocate csrD
-- Select the results
select ISNULL(cast(id as CHAR(1)), '') as id
, ISNULL([Data 1], '') as 'Data 1'
, ISNULL([Data 2], '') as 'Data 2'
, ISNULL([Data 3], '') as 'Data 3'
, ISNULL([Data 4], '') as 'Data 4'
from #temp
-- More cleanup!
drop table #temp

Not the prettiest, but it was fun to figure out. If anyone has a better solution than my brute-force one, please post it. If nothing else, my solution is a nice mini-tutorial on how to build cursors! :D

[adam_k]

@BitBlt: Extra points for keeping it simple! :D Good job, I admire your patience.
I guess my derived tables could be joined to a new derived table with an integer column from 1 to 1000 or whatever. I believe I've seen this somewhere (and have copied it in my library of scripts I might never use, but are good to get ideas as to how geniuses think).
It's 12:39 am local time (Greece) , so I won't go searching for it. If OP doesn't like the cursor and can work with my derived queries I'll get it working tomorrow.
Good night everybody.

[BitBlt]

Joke's on us! Apparently the OP cross-posted, and he's really using mySQL, and didn't give all the reqirements! (see this thread ). Here's what he REALLY expects:

Table Result
ID	|	Data 1	|	Data 2	|	Data 3	|	Data 4
-----------------------------------------------------------------------
1	|	A	|	B	|	D	|	G
	|      null	|	C	|	E	|	H
	|      null	|      null	|	F	|      null
2        |         W       |         X        |       null      |       Z

However, it did make me re-think my solution above to possibly solve the extra requirement, and here's what I came up with...uglier and sicker than before, but IT WORKS!

-- Prepare a temp table
create table #temp(id int, [Data 1] char(1) null, [Data 2] char(1) null, [Data 3] char(1) null, [Data 4] char(1) null)
-- Declare local variables
declare @aId int , @aVal char(1), @bId int , @bVal char(1), @cId int , @cVal char(1), @dId int , @dVal char(1) 
declare @Id int
-- Have to start with a list of ID's to iterate through...
declare outerCursor cursor for 
select distinct id from dbo.a
union 
select distinct id from dbo.b
union 
select distinct id from dbo.c
union 
select distinct id from dbo.d
-- Let the iterations begin!
open outerCursor
fetch next from outerCursor into @Id
while @@FETCH_STATUS = 0
begin
	-- Declare cursors
	declare csrA  cursor for select * from dbo.a where id = @id
	declare csrB  cursor for select * from dbo.b where id = @id
	declare csrC  cursor for select * from dbo.c where id = @id
	declare csrD  cursor for select * from dbo.d where id = @id
	-- Open the cursors
	open csrA
	open csrB
	open csrC
	open csrD
	-- Get the first row from each cursor
	fetch next from csrA into @aId, @aVal
	fetch next from csrB into @bId, @bVal
	fetch next from csrC into @cId, @cVal
	fetch next from csrD into @dId, @dVal
	-- Start looping 
	while 1 = 1
	begin
		-- Check to see if there's any data...if not, get out of the loop
		if @aId is null and @bId is null and @cId is null and @dId is null
			break
		-- Insert a row
		insert into #temp (id, [Data 1], [Data 2], [Data 3], [Data 4])
		values (@aId, @aVal, @bVal, @cVal, @dVal)
		-- Get the next row of each cursor.  
		-- @@FETCH_STATUS <> 0 means no row, so null out the local variables
		fetch next from csrA into @aId, @aVal
		if @@FETCH_STATUS <> 0
			select @aId = null, @aVal = NULL
		fetch next from csrB into @bId, @bVal
		if @@FETCH_STATUS <> 0
			select @bId = null, @bVal = NULL
		fetch next from csrC into @cId, @cVal
		if @@FETCH_STATUS <> 0
			select @cId = null, @cVal = NULL
		fetch next from csrD into @dId, @dVal
		if @@FETCH_STATUS <> 0
			select @dId = null, @dVal = NULL
	end
	-- Close and clean up! 
	close csrA
	deallocate csrA
	close csrB
	deallocate csrB
	close csrC
	deallocate csrC
	close csrD
	deallocate csrD
	-- Get the next ID from the outer cursor...
	fetch next from outerCursor into @Id
end
-- Select the results
select ISNULL(cast(id as CHAR(1)), '') as id
, ISNULL([Data 1], '') as 'Data 1'
, ISNULL([Data 2], '') as 'Data 2'
, ISNULL([Data 3], '') as 'Data 3'
, ISNULL([Data 4], '') as 'Data 4'
from #temp
-- More cleanup!
drop table #temp

Here are my results:

id	Data 1	Data 2	Data 3	Data 4
1	A	B	D	G
 	 	C	E	H
 	 	 	F	 
2	W	X	 	Z

This appears to be a general solution to the question posed. I will leave it as an exercise to the OP to figure out if he can do this with mySQL. Good luck to you! And please don't cross-post anymore!

[gennesis]

i think you have to use another select statement to get records according to your required format ,

select ID,Data1 ,Data2,Data3,Data4
from(
select ID,Data1 ,Data2,Data3,Data4
from (
select tA.id,tA.Data1 as Data1,"" as Data2,"" as Data3,"" as Data4
from TableA tA
group by tA.ID
union all
select tB.id,"" as Data1,tB.Data2 as Data2,"" as Data3,"" as Data4
from TableB tB
group by tB.ID
union all
select tC.id,"" as Data1,"" as Data2,tC.Data3 as Data3,"" as Data4"
from TableC tC
group by tC.ID
union all
select tD.id,"" as Data1,"" as Data2,"" as Data3,tD.Data4 as Data4
from TableD tD
group by tD.ID
) as n 
group by id,Data1,Data2,Data3,Data4
) as main
group by id,Data1,Data2,Data3,Data4
order by id,Data1,Data2,Data3,Data4

I think this time it will provide the same result you want , Regards

M.Waqas Aslam

Thanks to you all for this effort i will try this i never expect that my post is that complicated to solve. I was thinking that this could only be solve by a series of joins. but nevertheless thanks again i will let you know if it works on my end.

[gennesis]

Thanks to you all for this effort i will try this i never expect that my post is that complicated to solve. I was thinking that this could only be solve by a series of joins. but nevertheless thanks again i will let you know if it works on my end.

Thanks guys for helping me. the idea of a dummy table has a great help for me. Thanks again...