-1

Here I have a table that has many rows and I ultimately want a final result where the rows are output based off of matching criteria. I would like to extract the Rows / or Col1 where Code includes values '1' and '3' together. I was looking to also add DISTINCT but not sure if needed because the initial table has many duplicate rows.

Col1 Col2 Code
A 132 1
A 132 3
B 141 3
C 149 2
D 100 7

The table would look like the table below:

Col1 Col2 Code
A 132 1
A 132 3

Edit. I've tried code similar to this from another posting. I modified it to cater to what I was looking for but was getting an error when using having and found the results were missing some rows I was expecting. SELECTING with multiple WHERE conditions on same column

SELECT T1.contact_id FROM your_table T1 JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded' -- // more joins if necessary WHERE T1.flag = 'Volunteer'

Kamealon1234
  • 9
  • 5

1 Answers1

1

You can use subqueries. I'm not sure if you want other rows, but something like this:

select t.*
from t
where exists (select 1 from t t2 where t2.col1 = t.col1 and t2.col2 = t.col2 and t2.code = 1) and
      exists (select 1 from t t2 where t2.col1 = t.col1 and t2.col2 = t.col2 and t2.code = 3) ;

If you just want the col1/col2 pairs where this is true:

select col1, col2
from t
where code in (1, 3)
group by col1, col2
having count(distinct code) = 2;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for the quick answer, if I would like to have more columns included would that conflict with the HAVING Clause? or do I just need to include it in the GROUP BY. Can you explain the having .. = 2 part as well? – Kamealon1234 Jul 29 '21 at 18:36
  • This actually helped steer me on the right path but was curious to know why it shows no results when you have more columns. – Kamealon1234 Jul 29 '21 at 20:43
  • @PTurbo75 . . . I don't know. This answers the question you have here. If you have a different question with other columns, perhaps you should ask a new question. The `= 2` is the number of values. It says that both codes are in the group defined by `col1`/`col2`. – Gordon Linoff Jul 29 '21 at 22:43
  • Could you elaborate on this select t.* from t where exists (select 1 from t t2 where t2.col1 = t.col1 and t2.col2 = t.col2 and t2.code = 1) and exists (select 1 from t t2 where t2.col1 = t.col1 and t2.col2 = t.col2 and t2.code = 3) ; – Kamealon1234 Aug 23 '21 at 21:21
  • @Kamealon1234 . . . That checks if each of the codes exist for the col1/col2 combination. – Gordon Linoff Aug 23 '21 at 22:48