Custom SQL response (JOIN TABLE)

I have two tables, table1 and table2

I want to link the two tables with the id, by grouping the information in one and the same answer.

table1:

╔════╦══════════════╦
║ id ║  product_id  ║
╠════╬══════════════╬
║  1 ║      123     ║
║  2 ║      456     ║
║  3 ║      789     ║
╚════╩══════════════╩

table2:

╔════╦══════════════╦══════════════╦
║ id ║    status    ║     date     ║
╠════╬══════════════╬══════════════╬
║  1 ║   received   ║    02/20     ║
║  1 ║   shipped    ║    03/20     ║
║  2 ║   received   ║    04/20     ║
║  2 ║   shipped    ║    05/20     ║
║  3 ║   received   ║    06/20     ║
║  3 ║   shipped    ║    07/20     ║
╚════╩══════════════╩══════════════╩

I want this output:

╔════╦══════════════╦══════════════╦══════════════╦
║ id ║    r_date    ║   s_date     ║  product_id  ║
╠════╬══════════════╬══════════════╬══════════════╬
║  1 ║     02/20    ║    03/20     ║     123      ║
║  2 ║     04/20    ║    05/20     ║     456      ║
║  3 ║     06/20    ║    07/20     ║     789      ║
╚════╩══════════════╩══════════════╩══════════════╩

How Can i have this result ?

Answer

You can use two LEFT JOINs, as in:

select
  a.id,
  r.date as r_date,
  s.date as s_date,
  a.product_id
from table1 a
left join table2 r on r.id = a.id and r.status = 'received'
left join table2 s on s.id = a.id and s.status = 'shipped'

Advertisement

Answer