1

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.

  • Thanks! a_horse_with_no_name for removing the thankyou's :) – user2964143 Dec 21 '13 at 16:49
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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