Daniel
Daniel

Reputation: 21

Removing Rows with Duplicate Values Across Multiple Columns

I have query like below:

SELECT
    c.id, 
c.user,
c1.user,
c2.user
FROM (
    SELECT 
    id,
    user
    FROM 
        table_x
) c
INNER JOIN table_x c1 ON c.id = c1_id AND c.user = 'steve'
INNER JOIN table_x c2 ON c.id = c2.id AND c1.user = 'rob'
INNER JOIN table_x c3 ON c.id = c3.id AND c2.user LIKE 'r%'
GROUP BY c.id, c.user, c1.user, c2.user

And it can produce a result set like:

id | user | user | user
1    steve  rob    rob52
1    steve  rob    rob

I need the result set to not include the second row where the user across two columns is not unique. Is there a way to check for this without using a where clause to check every individual combination of columns?, because when the result set spans to something like 6 columns, it would be just too much to check for.

It is also possible for a result set to come back as:

id | user | user | user
1    rob    steve  rob

So a comparison with the <> or != operator at the time of join would not catch a row like this but could row 2 in the above result set.

Thanks

Upvotes: 1

Views: 153

Answers (2)

Chris Travers
Chris Travers

Reputation: 26454

what's wrong with just:

WHERE c.user <> c1.user AND  c1.user <> c2.user AND c2.user <> c.user

that seems to do exactly what you are looking to do. I think you are overthinking your problem.....

Upvotes: 0

Jeroen
Jeroen

Reputation: 327

You could use distinct on

SELECT DISTINCT ON (c.id, c.user, c1.user)
    c.id, 
c.user,
c1.user,
c2.user
FROM (
    SELECT 
    id,
    user
    FROM 
        table_x
) c
INNER JOIN table_x c1 ON c.id = c1_id AND c.user = 'steve'
INNER JOIN table_x c2 ON c.id = c2.id AND c1.user = 'rob'
INNER JOIN table_x c3 ON c.id = c3.id AND c2.user LIKE 'r%'
GROUP BY c.id, c.user, c1.user, c2.user

This way you will only get 1 record for each distinct combination of the columns mentioned in the distinct on clause

Upvotes: 1

Related Questions