CODEWITHSUNDEEP
sqloracle-database
TPR
TPR

Reputation: 2577

oracle sql - join with having with and group by?

If I have to find all uses that viewed the same video more than once, then I'm using the following query. 

SELECT userid AS users,video_id 
FROM watching_list GROUP BY userid , video_id HAVING COUNT(*) > 1;

^^It does give you that, right? If watching_list contains userid and videoid that the user watched and datetime stamp.

What I would like to do is join it to user table, based on userid, to look up user's name etc too and not just the userid.

I tried doing simple join, and of course it broke over me.

Upvotes: 0

Views: 12854

Answers (2)

Justin Pihony
Justin Pihony

Reputation: 67115

SELECT Users.Name, Users.userid AS users,video_id 
FROM watching_list 
    JOIN Users
        ON Users.UserID = watching_list.UserID
GROUP BY Users.userid , video_id, Users.Name
HAVING COUNT(*) > 1;

Why would this break? It should be the same user, so adding additional info of the same person to the group by should not cause a change

Here is a crude, quick SQLFiddle to prove this

Upvotes: 4

Marc
Marc

Reputation: 11633

A somewhat inelegant way to do it is like so:

Select USERS.LAST_NAME,
       USERS.FIRST_NAME,
       double_watchers.VIDEO_ID
  FROM USERS,
      (SELECT userid AS users,
              video_id 
         FROM watching_list
        GROUP BY userid , video_id
       HAVING COUNT(*) > 1) double_watchers
Where double_watchers.userid = users.id;

I'm creating an inline table (not sure about terminology) with your original query and then joining it to the USERS table. There's probably a much more efficient way to do it, but I don't have a test instance in front of me to experiment right now.

Upvotes: 3

Related Questions