Extract tuples with specified common values in another column in SQL

Question

I have a dataset that look like:

 Col1    Col2    
 1        ABC 
 2        DEF
 3        ABC 
 1        DEF 

Expected output:

Col1     Col2    
 1        ABC 
 1        DEF

I want to extract only those IDSs from Col1 which have both values ABC and DEF in the column.

I tried the self-join in SQL but that did not give me the expected result.

SELECT DISTINCT Col1
FROM db A, db B
WHERE A.ID <> B.ID
    AND A.Col2 = 'ABC'
    AND B.Col2 = 'DEF' 
GROUP BY A.Col1

Also, I tried to the same thing in R using the following code:

vc <- c("ABC", "DEF")
data1 <- db[db$Col2 %in% vc,]

Again, I did not get the desired output. Thanks for all the pointers in advance.

Answer 1

In R, you could do

library(dplyr) 
df %>% 
   group_by(Col1) %>% 
   filter(all(vc %in% Col2))

#   Col1 Col2 
#  <int> <fct>
#1     1 ABC  
#2     1 DEF  

---

The Base R equivalent of that would be

df[as.logical(with(df, ave(Col2, Col1, FUN = function(x) all(vc %in% x)))), ]

#  Col1 Col2
#1    1  ABC
#4    1  DEF

We select the groups which has all of vc in them.

Answer 2

In R, we can also use data.table

library(data.table)
setDT(df)[, .SD[all(vc %in% Col2)], by = col1]