edgelord edgelord - 1 month ago 8
SQL Question

prevent duplicate results when joining the same query postgresql

I have to join the same query in postgresql, but I only need one row per couple of columns; i.e. I don't want a row containing column1 -- column2, as well as a row containing column2 -- column1.

Example:

WITH q AS

(...)

SELECT q1.title AS title1, q2.title AS title2
FROM
q AS q1
INNER JOIN
q AS q2 USING(id)
WHERE q1.title != q2.title


EDIT: Assuming multiple titles can be recognised by the same id (kind of a bad choice of name)

So q is a predefined query, which has two columns, being "title" and "id". Combining them this way logically gives me these results:

title1 -- title2

x1 -- y1

y1 -- x1

...

xn -- yn

yn -- xn


etc. But I only need the
xi -- yi
sets
(0 < i <= n)
.

What would be the best way to do this?

Answer

Just change != for <

WHERE q1.title < q2.title