CODEWITHSUNDEEP
mysqlsqlduplicatesinner-join
CoffeeCoffeeBuzzBuzz
CoffeeCoffeeBuzzBuzz

Reputation: 105

Select only duplicates from inner joined table

So I am very new to sql and need some help. I have an inner joined code of 3 tables:

SELECT *
FROM Activity 
Inner UserActivity on UserActivity.activity_id = Activity.activity_id 
Inner Join User on UserActivity.user_id = User.user_id 
;

From this code it creates a mega table, which is great. But now I want to select only the duplicate entries for 6 columns, activity_name, activity_time, user_id, email, first_name, and last name. I would like to group these by activity_time.

These specific columns look like:

activity_name  activity_time   user_id   email           first_name   last_name
Reading        00:00:15        10001     [email protected]   Mike         Porter
Share          00:01:10        10765     [email protected]    Jamie        Hollands
Like           23:15:00        15761     [email protected]   Ragav        Taylor
Tweet          07:59:01        17001     [email protected]   Julie        Smith

How can I do this?

Thank you!

Upvotes: 0

Views: 562

Answers (1)

Rohit Gupta
Rohit Gupta

Reputation: 455

You can use a COUNT, GROUP, HAVING combination to filter duplicate rows. 

SELECT  activity_name, activity_time, user_id, email, first_name, last name, COUNT(*)
FROM Activity 
INNER JOIN UserActivity on UserActivity.activity_id = Activity.activity_id 
INNER JOIN User on UserActivity.user_id = User.user_id 
GROUP BY activity_name, activity_time, user_id, email, first_name, last name
HAVING COUNT(*)>1

Upvotes: 4

Related Questions