Postgresql – Append columns with values from has many reletionship

aliasjoin;postgresql

I have two tables orders and items. Every Order has many Items.

orders
- id
- created_at
- paid_at

items
- id
- title
- amount
- quantity
- order_id

Let say that there are 3 kinds of items. Chair, table and door.

I would like to create a query that will produce table with following columns:

- order_id
- created_at
- paid_at
- item_id # for chair
- item_title
- item_quantity
- item_amount
- item_id # for table
- item_title
- item_quantity
- item_amount
- item_id # for door
- item_title
- item_quantity
- item_amount

If order has chair item, data in columns for chair will be displayed otherwise it will be empty.

Why am I doing this?

I would like to export this data to excel sheet.

There is limited amount of different items so there is no problem with milions of columns.

Best Answer

You can join to a single table multiple times. Just use different aliases.

And use LEFT [OUTER] JOIN to accommodate for the possibility of missing rows.

Assuming there can only be a single instance of chair, table and door attached to each order. You did not specify that, but your question would hardly make sense otherwise.

I introduce item_type in the table items to make this work. Consider the schema in the fiddle.

SELECT o.id AS order_id, o.created_at, o.paid_at
      ,c.id       AS chair_id
      ,c.title    AS chair_title
      ,c.quantity AS chair_quantity
      ,c.amount   AS chair_amount

      ,t.id       AS table_id
      ,t.title    AS itable_title
      ,t.quantity AS table_quantity
      ,t.amount   AS table_amount

      ,d.id       AS door_id
      ,d.title    AS door_title
      ,d.quantity AS door_quantity
      ,d.amount   AS door_amount
FROM   orders o
LEFT   JOIN items AS c ON c.id = o.id AND c.item_type = 'chair'
LEFT   JOIN items AS t ON t.id = o.id AND t.item_type = 'table'
LEFT   JOIN items AS d ON d.id = o.id AND d.item_type = 'door';

Use distinct column aliases to make this work.

-> SQLfiddle demo