SQL, matching all the values in column b and return column a for those matches

Question

I'm only looking at one table, I have the values I need to match, so.

Col A / Col B
1 / 1
1 / 2
1 / 3
2 / 2
2 / 3
4 / 1
4 / 3

So the values I pass in would be 1 and 3 and I'd want to return 1 and 4.

I've tried a group by with a could, where I've had to use two sub queries, but it didn't work.

EDIT: Ideally I'd like to use a select query as I need to use this query within another query which I'm building up outside of sql server.

EDIT2: Ideally I'd like to pass in my input as a csv string

what is your criteria for input and output. please provide more explanation. — Romil Kumar Jain, May 03 '12 at 10:45
the criteria for input would be `where col_B IN (1,3)` and output would be `select Col_A` — Jules, May 03 '12 at 10:47

score 4 · Accepted Answer · answered May 03 '12 at 10:50

4

select ColA 
from your_table
where ColB in(1, 3)
group by ColA
having count(ColA) > 1

answered May 03 '12 at 10:50

juergen d

Why do you have the `HAVING` statement in there :/ – Stuart Blackler May 03 '12 at 10:55
1

@StuartBlackler: Because without it, it would return all ColA's that have **either** `1` or `3` as value. But the OP needs ColA's with **both** – juergen d May 03 '12 at 10:56
Its because of the requirement to show the values in ColA that satisfy BOTH ColB `where` clauses. Having said that i dont think this will work when there is more than two input criteria – Simon May 03 '12 at 10:57
Your `HAVING` clause should be `HAVING COUNT(DISTINCT ColB) = @Criteria`, Where @Criteria is the number of different requirements for colB. – GarethD May 03 '12 at 11:09
@GarethD: Not if ColA and ColB form a composite key, or their uniqueness is ensured through some other method. – weenoid May 03 '12 at 11:18
@weenoid A composite key, or unique constraint would only remove the need for the `DISTINCT` key word. The OP has stated that they "could be passing in numerous input values", so if there were 3 values passed `HAVING COUNT(ColA) > 1` would not work, it would have to be `HAVING COUNT(ColA) > 2` which is why I have suggested the change. – GarethD May 03 '12 at 11:23
@GarethD: Ah, gotcha. That's kind of implied, I would hope the OP has enough common sense to figure it out ;). – weenoid May 03 '12 at 11:29

score 0 · Answer 2 · answered May 03 '12 at 10:48

0

If I've understood correctly the values passed in relate to ColumnB and the return values come from ColumnA:

SELECT      A.ColumnA
FROM        [YOUR TABLE] A
INNER JOIN  [YOUR TABLE] ON ( A.ColumnA = B.ColumnA AND B.ColumnB = 1 )
WHERE       A.ColumnB = 3

answered May 03 '12 at 10:48

weenoid

Thanks, however, I could be passing in numerous input values :( – Jules May 03 '12 at 10:51
How are you passing the values in? Could you provide some code? – weenoid May 03 '12 at 10:52

2 Answers2