Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Thanks! a_horse_with_no_name for removing the thankyou's :) –  user2964143 Dec 21 '13 at 16:49
add comment

1 Answer

up vote 2 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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