Reputation: 21
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
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
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