CODEWITHSUNDEEP
sqlself-join
Георгий Мальцев
Георгий Мальцев

Reputation: 1

That in self-joining, each product forms a pair with itself ("A-A"), and each pair occurs twice ("A-B" and "B-A"). Get rid of these two problems

select * from (
  select * from (
    select *, unnest(product_ids) as product_id 
    from orders
  ) t1
  inner join (
    select *, unnest(product_ids) as product_id_1 
    from orders
  ) t2 on t1.order_id = t2.order_id
) t3

enter image description here

i want to combine product_id and product_id_1 values ​​into an array and get rid of unnecessary pairs (pair with itself ("A-A"), and each pair occurs twice ("A-B" and "B-A").)

Upvotes: -1

Views: 60

Answers (1)

Bohemian
Bohemian

Reputation: 425278

To filter out same values and mirrored doubled values, add this condition to the join:

product_id < product_id_1

So the whole query becomes:

select *
from (select * from
    (
    select *, unnest(product_ids) as product_id
    from orders
    ) t1
    inner join
    (
    select *, unnest(product_ids) as product_id_1
    from orders
    ) t2 on t1.order_id = t2.order_id
    and product_id < product_id_1
) t3

Upvotes: 2

Related Questions