Is there a way to get duplicate values in a column with specific values in another column?

Question

I have an sql table like this:

TABLE: Info

ID      |   JOB    |  Value
--------|----------|------------------
1       |   leader |  3
2       |   Host   |  212
1       |   User   | 4
2       |   leader |  5
2       |   Host   |  4

I'm trying to get the rows where a duplicate ID must contain 3 and 4 from the Value column. I'm currently using this query:

  select * from info where value = 3 and 4
   group by id having count(*) > 1;

my result using this query

ID     |   JOB    |  Value
--------|----------|------------------
null    |   null   |  null

whereas my expected result should be:

ID      |   JOB    |  Value
--------|----------|------------------
1       |   leader |  3
1       |   User   | 4

Please help as I'm not sure what I'm doing wrong

Syntax Error `where value = 3 and 4` try `where value = 3 and value = 4` — RiggsFolly, Jul 02 '20 at 16:36
If you are grouping by `id` then you are not going to get 2 rows with the same `id` — RiggsFolly, Jul 02 '20 at 16:38

score 0 · Answer 1 · answered Jul 02 '20 at 16:43

your expected result sample is not coherent with your question description .. the duplicated rows for value 3,4 are selected using

select id
from info
where value in  (3,4)
group by id 
having count(id)>1

then

select  * 
from  info 
inner join  (
    select id
    from info
    where value in  (3,4)
    group by id 
    having count(id)>1
) t on t.id = info.id

Is there a way to get duplicate values in a column with specific values in another column?

1 Answers1