Looping through values in one table for the where clause in another 1

[xcaliber2222]

Hello,

I've been researching this and it seems there are a few ways to approach this, but I'm not sure of the best way and am not sure how to code for it. So hopefully this makes sense. I am trying to accomplish the following:

select value1, value2 from table1

select value3, value4 from table2 where value4 in ('111', '222', '333')

The "IN" clause in the second query above needs to get its values from the "value2" of the first query. I've looked into a cursor loop (I've been told to always try to avoid this in favor of a set based solution), a while loop with temp or derived tables, and perhaps a user defined function to loop through the values in the first query to return the values into the IN clause in the second query, however, I need to return this all in one resultset. Also, I wasn't sure how to construct the single quotes and commas in the "IN" clause.

Any help would be greatly appreciated.

Thank you,
Alejandro

 

[markros]

In SQL Server 2005 and up you can use CTE solution for your problem.

Also it can be solved with UDF, though I'm not sure about performance.

Do you need more information on this or you can work it now on your own?

 

[r937]

select value3, value4 from table2
where value4 in
(select value2 from table1)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PWise]

select value3, value4 
from table2 
inner join (Select Distinct value2 from table1) as ti
on table2.Value4=table1.Value2

 

[markros]

Very nice.

 

[xcaliber2222]

Hello,

Thanks for the info. Maybe I'm not understanding or I oversimplified this, but don't I actually need to "loop" through the values in the first table and pass those values to the IN clause in the second table?

Alejandro

 

[xcaliber2222]

Oh, by the way, this is for SQL Server 2000. Thanks.

 

[bborissov]

No, you don't have to LOOP through values from the first select and pass them to the next query if you can do that with ONE SELECT. The example pwise is used is called DERIVED TABLE. In short you could use SELECT you could think of in both FROM and JOIN clauses.
Just Google derived table

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I was thinking of alternative solution and also

select value3, value4
from table2 where exists (Select 1 from table1 where value2 =table2.value4 )

should work the same.

I'm wondering to see the performance difference.

Good thinking, pwise.