Chris Chris - 3 months ago 14
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

feedback
column.

SELECT 1.firstname, 1.lastname, 2.feedback, 2.timemodified
FROM 1
INNER JOIN 2 ON 1.id = 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

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.feedback, 2a.timemodified,
    2b.feedback, 2b.timemodified
FROM 1 
INNER JOIN 2 as 2a ON 1.id = 2a.userid and 2a.feedback = 5
INNER JOIN 2 as 2b ON 1.id = 2b.userid and 2b.feedback = 6
ORDER BY 1.lastname

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

SELECT 1.firstname, 1.lastname
FROM 1
WHERE
EXISTS (SELECT * FROM 2 WHERE 2.userid = 1.id and feedback = 5)
AND
EXISTS (SELECT * FROM 2 WHERE 2.userid = 1.id 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.

Comments