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

Question

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

I wonder fi will not be better to aggregate colum device to string and in second query search by "like". What do you think? — Klick
– Klick, Commented Dec 16, 2021 at 5:04

ProDec · Accepted Answer · 2021-12-16 04:50:52Z

1

try this one

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

demo

answered Dec 16, 2021 at 4:50

ProDec

5,4001 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arun · Accepted Answer · 2021-12-16 04:49:54Z

0

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

answered Dec 16, 2021 at 4:49

Arun

1,1366 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Demo here

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related