Append columns with values from has many reletionship 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.
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