CODEWITHSUNDEEP
sqlpostgresql
edgelord
edgelord

Reputation: 83

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?

Upvotes: 1

Views: 56

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

Just change != for <

WHERE q1.title < q2.title

Upvotes: 2

Related Questions