CODEWITHSUNDEEP
postgresql
Rogier
Rogier

Reputation: 550

Show existing possible joins in result in Postgres

I have tables with Users (user_id, user_name), Products (product_id, product_name) and Views (user_id, product_id, timestamp) by these users of these products.

Now, I want to display a matrix, referencing all Products and all Users, with if they have seen the products (0 or 1). So, when having 2 users and 3 products, the result could like this:

+---------+------------+------+
| user_id | product_id | seen |
+---------+------------+------+
| 1       | 1          | 0    |
+---------+------------+------+
| 1       | 2          | 0    |
+---------+------------+------+
| 1       | 3          | 1    |
+---------+------------+------+
| 2       | 1          | 1    |
+---------+------------+------+
| 2       | 2          | 0    |
+---------+------------+------+
| 2       | 3          | 0    |
+---------+------------+------+

But how? I am only able to retrieve users and products that have been seen, because they are 'joinable' by the views table.

Upvotes: 0

Views: 18

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

Try cross joining the users and products table, then left join that to the junction table:

SELECT
    u.user_id,
    p.product_id,
    CASE WHEN v.timestamp IS NOT NULL THEN 1 ELSE 0 END AS seen
FROM Users u
CROSS JOIN Products p
LEFT JOIN Views v
    ON u.user_id = v.user_id AND p.product_id = v.product_id;

If the Views table could have more than one entry for a given user/product combination, and you only want to report a seen event once, then you may use SELECT DISTINCT with the above query.

Upvotes: 1

Related Questions