Postgres - select row if related table has two rows in condition-CodePudding

I have table 'devices' with following structure.

id | user_id | device

1 | 12 | tablet
2 | 12 | pc
2 | 12 | mobile

This table is related many to one with table "users". One user can have multiple devices, and I would like to select only users that has two devices "tablet" and "pc" for example.

How can I do it.

Thank you

CodePudding user response：

You can use multiple sub-queries to achieve this:

SELECT DISTINCT user_id
FROM devices
WHERE user_id NOT IN 
      (
      SELECT DISTINCT user_id
      FROM devices
      WHERE device IN
          (
          SELECT DISTINCT device
          FROM devices
          WHERE device NOT IN ('tablet', 'pc')
          )
      )
AND user_id IN
      (
      SELECT t.user_id
      FROM devices AS t
      INNER JOIN (SELECT user_id FROM devices WHERE device='pc') AS p
      ON t.user_id=p.user_id
      WHERE device='tablet'
      )

This is not an optimized query. We can use JOINS instead of WHERE to make it faster

Demo here

CodePudding user response：

try this one

select user_id
from devices
group by user_id 
having count(*) = 2 and max(device) = 'tablet' and min(device) = 'pc'

demo