[RESOLVED] [2005] SQL IN statement question

[stimbo]

I think I have an idea about how to do this, just looking for some input. I think I have to do 2 queries. The first query gets the distinct values in a table which returns a list of integer values. I would then want to use these numbers in the 2nd query.

Any number of values could be returned from query1. My question is how do I use that info in query 2? I think I have to use the IN statement. Do I have to use commandbuilder because I don't know how many results will be returned?

query1:

SELECT DISTINCT myCol1 FROM someTable

query2:

SELECT * FROM otherTable WHERE myCol IN (int1, int2, int3, int4.....)

How do I construct the IN part with the results returned from query1 ?

[jmcilhinney]

You simply take the first query and put it inside the parentheses in the second query.

A CommandBuilder is not for generating SELECT statements. It is for generating DELETE, INSERT and UPDATE statements from SELECT statements.

[stimbo]

I had no idea you could do that in SQL statements.

Thanks,