Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
So, if an order has 20 items, you want 20x4+3 = 83 columns? –  ypercube Oct 2 '13 at 18:00
    
How will you name the columns? –  Colin 't Hart Oct 2 '13 at 19:01
    
@ypercube you are correct. However, there are only 8 distinct items. –  tomazzlender Oct 3 '13 at 12:53
    
@Colin'tHart with arbitrary names: item1, item2... –  tomazzlender Oct 3 '13 at 12:54

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Thank you @Erwin Brandstetter! Exactly what I was looking for. –  tomazzlender Oct 3 '13 at 12:56
    
You can even generate queries of this form on the fly if the number of items should change regularly. –  Colin 't Hart Oct 3 '13 at 12:57
    
@Colin'tHart this is a great idea. Thank you. –  tomazzlender Oct 7 '13 at 11:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.