September 16th, 2013, 03:46 PM
Output based on multiple matches in column
I have joined 2 tables and able to output the proper rows but I want to filter it a bit more.
Basically outputting rows on multiple matches in the column.
If the table looks like this
c1 / c2
555 / black
555 / green
555 / white
555 / orange
555 / blue
222 / white
222 / blue
I would like the result showing this
555 / black
They both must be present to output columns 1 and 2.
Im playing around with "where exists" but not able to get the desired output.
Appreciate your help.
September 17th, 2013, 07:44 AM
I may be missing something but I can see no correlation between your two output examples and the expression 'multiple matches' - for example, 'black' only appears once in the first output example.
The moon on the one hand, the dawn on the other:
The moon is my sister, the dawn is my brother.
The moon on my left and the dawn on my right.
My brother, good morning: my sister, good night.
-- Hilaire Belloc
September 17th, 2013, 08:09 AM
where c2 in ('black','blue')
having count(distinct c2) = 2
September 17th, 2013, 05:27 PM
Hi, i apologize for not being clear, yes black shows once but also shows the other color blue, only if both of these are present the output should show both columns. In this case the id 555 has both colors so it outputs that.
222 only has blue but no black.
The code by swampboogie is working. Thank you very much.