join two tables, one column to multiple columns in sql

Question

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.

in your join statement, you can declare multiple conditions for join. This answer might help: https://stackoverflow.com/a/16597679/13121971 — UrbanR, Mar 01 '21 at 08:38
Show us some sample table data and the expected result - all as formatted text (no images.) I.e. [mcve]. — jarlh, Mar 01 '21 at 09:33

score 2 · Answer 1 · answered Mar 01 '21 at 08:40

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

score 0 · Answer 2 · answered Mar 01 '21 at 12:14

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;

join two tables, one column to multiple columns in sql

2 Answers2