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

Question

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?

1 and 2 are not acceptable terms for table/column identifiers. — Strawberry
– Strawberry, Commented Aug 23, 2016 at 16:12
If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry
– Strawberry, Commented Aug 23, 2016 at 16:13

Asaph · Accepted Answer · 2016-08-23 16:13:13Z

1

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.

edited Aug 23, 2016 at 16:13

answered Aug 23, 2016 at 16:07

Asaph

164k25 gold badges204 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

They're not my real table names, but no matter. Query worked. Thanks!

Kalrunoor · Accepted Answer · 2016-08-23 16:10:06Z

0

SELECT 1.firstname, 
       1.lastname
FROM 1
WHERE 1.id IN (SELECT 2.userid FROM 2)

answered Aug 23, 2016 at 16:10

Kalrunoor

766 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related