CODEWITHSUNDEEP
sqlpostgresql
nomnom3214
nomnom3214

Reputation: 259

join two tables, one column to multiple columns in sql

I would like to join two columns, lets say t1 and t2.

The serial_number column in t1 is equal to 3 columns in t2. Instead of writing different queries for each, I want to write them in one query.

My recent query is:

select
   t1.date,
   t1.type,
   count(t1.serial_number) as qty,
from t1 left join t2 on t1.serial_number = t2.serial_number_x
where true
   and t1.age > 2
group by
   t1.date,
   t1.type

The serial_number is also equal to serial_number_x, serial_number_y, serial_number_z in t2. How should I write my query in order to join all the serial_number columns of t2 in t1.

Thanks in advance.

Upvotes: 0

Views: 871

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

If I understand correctly, I think you want to unpivot the data and aggregate:

select t1.date, t1.type,
       count(v.serial_number) as qty
from t1 left join
     (t2 cross join lateral
      (values (t2.serial_number_x), (t2.serial_number_y), (t2.serial_number_z)
      ) v(serial_number)
     ) 
     on t1.serial_number = t2.serial_number
where tt1.age > 2
group by t1.date, t1.type;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

it seems you are finding multiple condition in join

select
   t1.date,
   t1.type,
   count(t1.serial_number) as qty,
from t1 left join t2 
on t1.serial_number = t2.serial_number_x
and t1.serial_number = t2.serial_number_y
and t1.serial_number = t2.serial_number_z
where 
    t1.age > 2
group by
   t1.date,
   t1.type

Upvotes: 2

Related Questions