Chris Chris - 1 year ago 85
MySQL Question

SQL: Filter duplicates out of result set based on two column values

I have a web site that collects feedback from two forms. I have a SQL query that returns the names of users who have submitted feedback through one or both of them. The feedback forms are given ID's 5 and 6 in the table's


SELECT 1.firstname, 1.lastname,, 2.timemodified
INNER JOIN 2 ON = 2.userid
ORDER BY 1.lastname

This returns a result set that looks like this:

firstname lastname feedback timemodified
--------- -------- -------- ------------
john doe 5 1471012069
john doe 6 1471012075
jane smith 5 1471013449
jane smith 6 1471055903
joe blow 6 1473058839
jim jones 5 1471033691

I am only interested in those users who have submitted feedback through BOTH forms (5 and 6). I would like to filter my result set so that anyone who has not is eliminated and those who have are listed only once. (e.g., the above result set should only show john doe and jane smith, one time each.) Ideas?

Answer Source

To only return a single row for each user that has submitted both feedback items 5 and 6, you can join your 2 table twice, filtering on the aforementioned feedback ids.

SELECT 1.firstname, 1.lastname,, 2a.timemodified,, 2b.timemodified
INNER JOIN 2 as 2a ON = 2a.userid and = 5
INNER JOIN 2 as 2b ON = 2b.userid and = 6
ORDER BY 1.lastname

You can also accomplish similar results using a WHERE EXISTS clause.

SELECT 1.firstname, 1.lastname
EXISTS (SELECT * FROM 2 WHERE 2.userid = and feedback = 5)
EXISTS (SELECT * FROM 2 WHERE 2.userid = and feedback = 6)

Note: I've preserved your highly unusual table names of 1 and 2 in my answer so that the answer matches the question, but I doubt these are your real table names.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download