CODEWITHSUNDEEP
sqlpostgresql
Carlos Garcia
Carlos Garcia

Reputation: 13

How to group by with two columns and viceversa

I have this kind of table

a_id | b_id
   1 |    2
   2 |    1
   2 |    1
   1 |    2
   3 |    1
   1 |    4

If its 1 I would like to get the different combinations that have a_id=1 or b_id=1 like

result
   2
   3
   4

Its like group by with two columns but using same values in each pair of columns (viceversa). Is it any possible way to handle this?

Upvotes: 1

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use union:

select b_id as result from t where a_id = 1 union
select a_id from t where b_id = 1;

There are other methods . . . say, a lateral joi:

select distinct x.result
from t, lateral
      (values (case when a_id = 1 then b_id else a_id end) x(result)
where 1 in (a_id, b_id);

Or even the query more directly:

select distinct x(case when a_id = 1 then b_id else a_id end) as result
from t
where 1 in (a_id, b_id);

Because you are removing duplicates, the distinct is going to dominate performance of pretty much any method.

Upvotes: 1

Related Questions