1

I have an issue with a postgres database that I am not sure how to handle. These are the tables (simplified obviously)

Users

+--+---------+
|id|user info|
+--+---------+
|10|{data}   |
|11|{data}   |
+--+---------+

Devices

+--+------+----------+
|id|userid|other info|
+--+------+----------+
|1 |10    |{data}    |
|2 |10    |{data}    |
|3 |11    |{data}    |
+--+------+----------+

Device_Preference

+--+------+------------+
|id|userid|preferenceid|
+--+------+------------+
|1 |10    |8           |
|2 |10    |9           |
|3 |11    |8           |
+--+------+------------+

Preferences

+--+---------------+
|id|preference info|
+--+---------------+
|8 |{data}         |
|9 |{data}         |
+--+---------------+

Preferences are linked to user, not device so I am looking for a query that gives me the preferences for every device, like this:

+--------+------+----------+
|deviceid|userid|prefenceid|
+--------+------+----------+
|1       |10    |8         |
|1       |10    |9         |
|2       |10    |8         |
|2       |10    |9         |
|3       |11    |8         |
+--------+------+----------+

I think a 'select Devices d1 join Devices d2 on d1.userid=d2.userid' is needed but I am not sure exactly how to proceed.

1 Answer 1

1
SELECT d.id deviceid, u.id userid, dp.preferenceid
FROM Users u, Devices d, Device_Preference dp
WHERE u.id = dp.userid
  AND u.id = d.userid
ORDER BY 1,3

fiddle

1
  • Thank you, this is exactly what I needed.
    – Tjeerd
    Dec 3, 2019 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.