Select from two lists - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Select from two lists

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2012-11-19 : 19:02:51

This may be continued from my last post but that was solved and ran into another problem.

I am getting too many rows back and I understand why but can't seem to be able to solve it.

	DECLARE @tmpTbl1 TABLE
	(
		id BIGINT
	);

	DECLARE @tmpTbl2 TABLE
	(
		id BIGINT 
	);	
		

INSERT INTO @tmpTbl1(id)VALUES(1)
INSERT INTO @tmpTbl1(id)VALUES(2)

INSERT INTO @tmpTbl2(id)VALUES(3)
INSERT INTO @tmpTbl2(id)VALUES(4)


	SELECT 	
	tbl1.id as tbl1
	, tbl2.id as tbl2
	FROM @tmpTbl1 as tbl1, @tmpTbl2 as tbl2

Of course the result is:

tbl1 tbl2
1 3
2 3
1 4
2 4

What I need is this because the first row in tbl1 will need to match the first row in tbl2 and so on:

tbl1 tbl2
1 3
2 4

The code above may be copied and ran for your convience.

thanks for any and all input!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-19 : 20:01:23

It is possible with ROW_NUMBER() OVER(PARTITION BY....) function but can you tell us why you need cross product or there is no matching field between two tables.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2012-11-20 : 00:06:46

It is actually an insert but in order to get it working I was doing a select....

INSERT INTO MyTable
(FirstFK]
,[SecondFK])
SELECT 
,fstLst.id
,sndLst.id
FROM @firstLIST as fstLst, @secondLIST as sndLst

Every row in both lists match. So, row 1 = row 1, and so forth....
Been stuck on this....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-20 : 01:14:51

You mean this - which assumes that your rows are in order of IDs. So if your IDs can be out of numeric sequence then this won't work. ie: (1,3,2)


select t1.id as t1, t2.id as t2
from   (
       select id, row_number() over (order by id) as rn	
       from   @tmpTbl1
       ) t1
join   (
       select id, row_number() over (order by id) as rn	
       from   @tmpTbl2
       ) t2 on t2.rn = t1.rn

OUTPUT:       
t1                   t2
-------------------- --------------------
1                    3
2                    4

Be One with the Optimizer
TG

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2012-11-20 : 11:58:59

After doing some testing today, there is still a problem.
Like I said, I was trying to get the code working using a select but it's actually for an insert.

I was expecting it to bring back one value at a time while inserting multiple rows, so each row will be inserted with the next one in the list using your code.

But it's bring back too many values at once.

This is going to be a stubborn one.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 14:46:13

You mean this

select t1.id as t1, t2.id as t2
from   (
       select id, row_number() over (order by id) as rn	
       from   @tmpTbl1
       ) t1 and t1.rn = 1
join   (
       select id, row_number() over (order by id) as rn	
       from   @tmpTbl2
       ) t2 on t2.rn = t1.rn

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-20 : 15:30:27

Well, this would just return the "first" row:

quote:

Originally posted by sodeep

You mean this

select t1.id as t1, t2.id as t2
from   (
       select id, row_number() over (order by id) as rn	
       from   @tmpTbl1
       ) t1 and t1.rn = 1
join   (
       select id, row_number() over (order by id) as rn	
       from   @tmpTbl2
       ) t2 on t2.rn = t1.rn and t1.rn = 1

But my question is why don't you want to insert all rows at once?
Perhaps an overview of what you're tying to accomplish would help us help you. Looks like a questionable design with what you've posted so far.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources