query problem

Hi,
I have to two colums
first colum second colum
1 0
2 0
3 0
4 1
5 1
6 2
7 2
8 3
9 3
10 4
11 5
What I need to do is retrieving all the records from "second colum" which equal to the records in "first colum" and the records in that "first colum" must have 0 in "second colum."
In short: I need to retrieve the records in second colum: 1, 1, 2, 2, 3, 3
Please, help me with that queryCan I just check:
Select the unique numbers from the second column, check that that number exists in the first column, then check if those rows have 0 as the second column?
|||Ugly as sin...
SELECT col1
FROM testTable
WHERE (col1 IN
(SELECT col1
FROM testTable
WHERE (col1 IN
(SELECT DISTINCT col2
FROM testtable)))) AND (col2 = 0)
|||in your example, i don't see any values in the second column that equal the first column...could you verify that?
the query would look something like the following though:
select second_column
from [yourTableName]
where second_columsn <> 0 AND second_column = first_column
|||Hey Jayson, I think you missed his question just by a bit...It isn'tRows where first column = second column, but where second column [values] are infirst column...Sort of a parent id type of thing.
Simplistically, I think this is what he's looking for:
SELECT [second column] FROM table
WHERE [second column] IN (SELECT [first column] FROM table WHERE [second column] = 0)