[RESOLVED] Query to Match 1 col to 2 values

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

Code:

---

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?...

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

Code:

---

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:

Code:

---

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)

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 :D Thanks again!