Postgresql – Formulating a Join Query for PostqreSQL

join;postgresql

I have a quick question, if someone can help, I would be grateful

I have the following tables
User, Video, User_video

user has userdetails (user_id, name, email)
video has videoDetails (video_id, video_url, video_price)
user_video is the mapping table which has (user_id,video_id,price,dttm)

currently, if I do a select from video table, it gives me all available videos which is fine. The current return format is as follows:

123  |  Video 1  | desc of video1 | /abvu274vasag.3gp | 5.00 
124  |  video 2  | desc of video2 | /dh63ddh3dag.3gp  | 6.00

So far, so good, but now, I need to get the details of the logged in user, as in which videos he has purchased and which ones he has not, so in the above response, next to each video, I need a "Y" or "N" flag (by referencing the user_video) table.

I need the output as follows:

123  |  Video 1  | desc of video1 | /abvu274vasag.3gp | 5.00 | Y
124  |  video 2  | desc of video2 | /dh63ddh3dag.3gp  | 6.00 | N

It is a PostgreSQL database. And I am not an expert on databases, I am familiar with only the basics.

If someone can guide me in this regards, it will be most helpful.

Best Answer

Assuming (video_id, user_id) is unique in user_video, a plain LEFT JOIN would do the job:

SELECT v.*, uv.video_id IS NOT NULL AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id;

$current_user_id being the ID of the current user.

If (video_id, user_id) is not unique, you could add GROUP BY

SELECT v.*, count(uv.video_id) > 0 AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id
GROUP  BY v.video_id;

count() doesn't count NULL values.
Or use an EXISTS semi-join to avoid duplicates:

SELECT v.*, EXISTS (SELECT 1 FROM user_video uv 
                    WHERE  uv.video_id = v.video_id
                    AND    uv.user_id = $current_user_id) AS has_bought
FROM   video v;

Or use the PostgreSQL-specific DISTINCT ON, that's particularly handy if you need the result in a certain sort order anyway.

SELECT DISTINCT ON (v.video_id)
       v.*, uv.video_id IS NOT NULL AS has_bought
FROM   video v
LEFT   JOIN user_video uv ON uv.video_id = v.video_id
                         AND uv.user_id = $current_user_id
ORDER  BY v.video_id, uv.video_id;  -- NULL sorts last
Related Question