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

Question

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").)

As per the [ask], please [**do not** post images of code, data, error message, etc](https://meta.stackoverflow.com/questions/285551). Instead, copy or type the text into your question, [formatted as code](https://meta.stackoverflow.com/questions/251361#251362). Reserve the use of images for diagrams or demonstrating rendering bugs; things that are impossible to describe accurately via text. — Bohemian, Apr 15 '23 at 01:23
[How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] [mre] — philipxy, Apr 15 '23 at 12:06

Bohemian · Answer 1 · 2023-04-15T01:22:00.993

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

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

1 Answers1