combine tables without common rows

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)

combine tables without common rows

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-21 : 16:04:00

I have 3 tables which i want to put together like this

6776 3.4 123.2
6695 4.5 343.2
6768 3.534 342.22

The first column is from Table 1, second from table 2, so on...

There must be some simple join, merge or combine right ?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-21 : 16:17:45

Not really if don't have common column in those tables.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-21 : 16:20:34

so how i add one table into another table

if i have
123
234

and
ABC
DEF

How would i add them to have
123 ABC
234 DEF

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-21 : 17:00:27

[code]
Select *
From
(
select Row_Number() over (order by mycolumn) as ROwID,MyColumn
From Tbl1
) a
inner Join
(
select Row_Number() over (order by mycolumn) as ROwID,MyColumn
From Tbl2
) b
on a.RowID = b.RowID
inner Join
(
select Row_Number() over (order by mycolumn) as ROwID,MyColumn
From Tbl3
) c
on b.RowID = c.RowID

[/code]
Use left joins if the # of rows in each table are not equal.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-21 : 17:26:19

Vinnie,
Thanks for the help.

The part
select Row_Number() over (order by BC) as ROwID,BC

unfortunately rearrange my ID column and as a result, the result are not matching correctly.

Thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 18:48:26

If you have an ID column in each table, then you would join on that..


Select table1.col2, table2,col2, table3.col2
FROM Table1 left join table2 on table1.IDcol = table2.IDCol
Left join table3 on table1.IDcol = table3.idcol

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-21 : 19:03:51

quote:
Originally posted by dataguru1971

If you have an ID column in each table, then you would join on that..

Select table1.col2, table2,col2, table3.col2
FROM Table1 left join table2 on table1.IDcol = table2.IDCol
Left join table3 on table1.IDcol = table3.idcol

Thanks,

I do know that. The problem is each table only has one column and no common id. Just like when you put together columns in Excel, but this is in SQL ;)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 19:07:57

Then modify the original solution to Order by your ID column..

Not sure what you plan on getting as connecting 3 tables without a common Id is like mixing 3 types of mud and hoping to get cake.


Select *
From
(
select MyColumn
From tbl1 order by [ID]
) a
inner Join
(
select MyColumn
From tbl2 order by [ID]
) b
on a.ID = b.ID
inner Join
(
select MyColumn
From tbl3 order by [ID]
) c
on b.ID = c.ID

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-21 : 19:36:12

Thanks for taking time out to answer my queries.

The first table is like

1
5
2

The second table is
A
B
C

The solution almost work because when you do order by [ID], it will rearrange the first table to
1
2
5
and have this result
1 A
2 B
5 C

when I should have
1 A
5 B
2 C

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 19:37:44

Then take out the order by clause of all of them.

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-21 : 19:51:08

Are you refering to the original solution or your solution ?
If i take out the order by clause, how would I inner join them ? I have no common column to use the ON condition

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 19:58:30

quote:
Originally posted by daman

how would I inner join them ? I have no common column to use the ON condition

That my friend is the all important question.

YOu don't have a way to join them, so either you have to use the ID column, or you have to use the 1st solution.

What you seem to be missing is that unless there is a defined way to connect the rows to eachother ( a common link of some kind), it can't be done.

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-21 : 20:10:41

Ok, now i'm back at squared one ;)

I really appreciate your patience and help. Let's see if we can get this to work again

Using Vinnie solution, is there a way to generate ID without order the data column ?

So for table 1, i will have

Id Value
1 1
2 5
3 2

And for table 2
Id Name
1 A
2 B
3 C

Now we can use inner join on the ID. I'm sorry if I miss something very obvious in your solution.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 20:17:14

Wait, do you HAVE an ID column or not?

If you have an ID column, just JOIN on that without any ordering necessary.

If you don't have an ID column, then that changes things quite a bit.

you can use Vinnie's without the order by part of the OVER clause I think..


Select *
From
(
select Row_Number() over (mycolumn) as ROwID,MyColumn
From Tbl1
) a
inner Join
(
select Row_Number() over (mycolumn) as ROwID,MyColumn
From Tbl2
) b
on a.RowID = b.RowID
inner Join
(
select Row_Number() over (mycolumn) as ROwID,MyColumn
From Tbl3
) c
on b.RowID = c.RowID

As I understand the Order by clause is optional...and in theory may return the rows in the order they are stored in.

I don't have 2005 box, so I am not entirely sure how the function works, but BOL helps.

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-21 : 20:22:36

I don't have ID column, that's the reason Vinnie using that to create temp ID column for me

I just tried OVER () without order by....but got syntax error...i think it's required. I'm on SQL 2005 by the way.

So i guess we will have to use a counter of some kind of create a temp id...

Good thing is that all these table have the same number of rows ;)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 20:25:33

I think it should be OVER (Partition by Mycolumn)

http://msdn2.microsoft.com/en-us/library/ms189461.aspx

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 20:28:44

You can add an identity column to each table and
Alter Table [Table] Add RowID int null

Create Table #RowTemp (RowID identity(1,1),MyColumn char(10))

Insert into #RowTemp
Select MyColumn
From [Table]

Update [Table]
Set RowID = #RowTemp.RowID
From #ROwTemp inner join [Table] on #RowTemp.MyColumn = [Table].MyColumn

something like that would essentially add the row number to the table..you can then just join on that...
Update [Table

Poor planning on your part does not constitute an emergency on my part.

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-23 : 11:44:36

It works beautifully now. I added RowId INT IDENTITY (1, 1) NOT NULL to each of the table and join them using RowID

Thanks for all the help and ideas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources