select from table where both id exist

Question

I need to select from a single table where both id exists in the column having matching id in another column. My query is like below which gives rows with even just single matching id.

select * from customer_appdata where appdata_id in(11,12) 

id  customer_id appdata_id  
6    65             4
7    65             12  
8    65             8   
9    66             11  
10   66             12

so here i just want last and second last rows(9,10) as they have both 11 and 12 with common id 66.

What does 11,12 have to do with 9,10 or 66? Confused. Please clarify your question. It is not clear. Please specify db platform. — OldProgrammer, Apr 23 '15 at 02:12
Can you have multiple records with the same `customer_id` and the same `appdata_id`? — PM 77-1, Apr 23 '15 at 02:14
`select * from customer_appdata where appdata_id in(11,12) AND customer_id = 66` no? Your question is really not clear. — plalx, Apr 23 '15 at 02:18
Do you want to find only customers that have only those appdata_ids, or customers that have them both (but can have additional ones)? — Lukáš Lalinský, Apr 23 '15 at 02:29

Lukáš Lalinský · Answer 1 · 2015-04-23T02:25:32.307

2

If I understand the problem correctly, this should work:

select * from customer_appdata where customer_id in (
  select customer_id from customer_appdata
  where appdata_id in (11,12) 
  group by customer_id
  having count(distinct appdata_id) = 2
)

You find all customer_ids that are repeated specific number of times (that's the inner query) and then select all rows with those customer_ids. There is probably a faster way, but if performance is not critical this is a simple way to solve the problem.

edited Apr 23 '15 at 02:25

answered Apr 23 '15 at 02:19

Lukáš Lalinský

40,587
6
104
126

I edited the answer, because I just now realized the real problem you want to solve. The first version would be doing something different than you expect. – Lukáš Lalinský Apr 23 '15 at 02:27
you need to change having count(distinct appdata_id) = 2 to having count(appdata_id) > 2. appdata_id can be more than 2 – Ari Djemana Apr 23 '15 at 02:32
1

distinct appdata_id can't be more than two, because I'm filtering them on only two values. This is needed to make sure I have matched both 11 and 12 in the group. – Lukáš Lalinský Apr 23 '15 at 02:34

score 0 · Accepted Answer · answered Apr 23 '15 at 02:57

This query selects rows with customer_appdata of 12 or 11 and uses exists to see if the other row is also in the table.

select * from customer_appdata c1
where appdata_id in (11,12)
and exists (
    select 1 from customer_appdata c2
    where c2.appdata_id in (11,12)
    and c2.appdata_id <> c1.appdata_id
    and c2.customer_id = c1.customer_id
)

select from table where both id exist

2 Answers2

Linked