[RESOLVED] Query to Match 1 col to 2 values

[SambaNeko]

I'm using SQL Server, and I have a table that - for the sake of example - looks something like this:

col1   col2
1      5
1      3
2      5
2      6
2      7

This is one table, two columns. If possible, what sort of query can I use to get a single return value from col1, where col2 has 2 or more particular values? For instance, supposing I have the values of 5 and 6 for col2, I'd expect this query to return 2, because 2 is the value in col1 for both col2=5 and col2=6.

Does that make sense?...

[si_the_geek]

Here's one way (limited to two items):

SELECT col1
FROM TheTable
WHERE col2 = 5
  AND col1 IN (
        SELECT col1 
        FROM TheTable
        WHERE col2 = 6
        )

.and here's another, which can cope with as many as you like:

SELECT col1, count(*)
FROM TheTable
WHERE col2 IN (5, 6)
GROUP BY col1
HAVING count(*) = 2

(you just need to add more to the IN list, and change the number at the end)

[SambaNeko]

Thanks! This query is being used as a sub-query, and the overall query is still not producing the desired result, but I think this part is correct now. I'll post again about the rest of the query if I can't figure it out myself.

EDIT: Alright, it's entirely working now Thanks again!