Inner Join OR Union - 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

General SQL Server Forums

New to SQL Server Programming

Inner Join OR Union

Author

Topic

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2007-01-10 : 15:58:13

Hi there SQLTEAM

I have a problem, and need your help.

table1 has 1 single field,example
pkiTownID
DATA
1
2
3
4

table2 has 1 single field, example
pkiTownID
DATA
6
7
8
9

What SQL Query should i run to merge or join these 2 tables into 1

The output that i would like it the following

DATA
1 6
2 7
3 8
9 9

Is this possible?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-10 : 16:12:41

SQL is a relational database -- how does table1 relate to table2 ? from the columns and data you have shown us, it doesn't, so what you are trying to do doesn't make a lot of logical sense unless you can explain a little more.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 16:15:56

[code]-- prepare sample data
declare @table1 table (pkiTownID int)

insert @table1
select 1 union all
select 2 union all
select 3 union all
select 4

declare @table2 table (pkiTownID int)

insert @table2
select 6 union all
select 7 union all
select 8 union all
select 9

-- Most versatile way
select x.pkitownid,
y.pkitownid
from (
select t1.pkitownid,
(select count(*) from @table1 as tt1 where tt1.pkitownid <= t1.pkitownid) seq
from @table1 as t1
) x
inner join (
select t2.pkitownid,
(select count(*) from @table2 as tt2 where tt2.pkitownid <= t2.pkitownid) seq
from @table2 as t2
) y on y.seq = x.seq

-- most obvious way
select t1.pkitownid, t2.pkitownid
from @table1 t1
inner join @table2 t2 on t2.pkitownid = t1.pkitownid + 5

-- fastest way
select pkitownid,
pkitownid + 5 pkitownid
from @table1[/code]

Peter Larsson
Helsingborg, Sweden

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2007-01-10 : 16:18:02

Thanks for the reply

This is my problem there is no physical relation, only that row 1 in table1 will allways link to table2 row 1 does this make any sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 16:25:10

Yes, I have already answered you!
Read the answers you are given and run them and test them...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 16:29:57

SQL Server 2005 way

select		x.pkitownid,
		y.pkitownid
from		(
			select	pkitownid,
				row_number() over (order by pkitownid) seq
			from	@table1
		) x
inner join	(
			select	pkitownid,
				row_number() over (order by pkitownid) seq
		) y on y.seq = x.seq
order by	x.seq

Peter Larsson
Helsingborg, Sweden

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2007-01-10 : 16:56:04

Hi Peter
Thanks alot, tried your sulotion and worked perfectly.
Yet again thanks alot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources