SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select from two lists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
286 Posts

Posted - 11/19/2012 :  19:02:51  Show Profile  Visit Zath's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/19/2012 :  20:01:23  Show Profile  Reply with Quote
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.
Go to Top of Page

Zath
Constraint Violating Yak Guru

USA
286 Posts

Posted - 11/20/2012 :  00:06:46  Show Profile  Visit Zath's Homepage  Reply with Quote
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....

Edited by - Zath on 11/20/2012 00:08:37
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/20/2012 :  01:14:51  Show Profile  Reply with Quote
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

Edited by - TG on 11/20/2012 01:17:27
Go to Top of Page

Zath
Constraint Violating Yak Guru

USA
286 Posts

Posted - 11/20/2012 :  11:58:59  Show Profile  Visit Zath's Homepage  Reply with Quote
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.



Edited by - Zath on 11/20/2012 12:57:48
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/20/2012 :  14:46:13  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/20/2012 :  15:30:27  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000