LEFT JOIN vs IF vs CASE vs SUBSELECTS

Question

SELECT

`model`,
`name`,
`category`,
`price`

FROM   `books`

I want to go from:

********************************************************************
*       model | name | category  | price          
********************************************************************
*       x     | y    | categoryA | price
*       b     | d    | categoryB | price
*       y     | u    | categoryB | price
*       d     | u    | categoryA | price
*       d     | u    | categoryB | price
*       d     | u    | categoryC | price

to:

********************************************************************
*       model | name | categoryA price | categoryB price | categoryC price       
********************************************************************
*       x     | y    | price cat A     | NULL            | NULL
*       b     | d    | NULL            | price cat B     | NULL
*       y     | u    | NULL            | price cat B     | NULL
*       d     | u    | price cat A     | price cat B     | NULL

I want to group model and name together and display their corresponding prices.

I've tried using left joins, subselects, case, if, etc... I am trying to find the most efficient way.

SELECT

`model`,
`name`,
(
case
when `category` = 'CategoryA'
then `price`
end
) as `CategoryA Price`,

(
case
when `category` = 'CategoryB'
then `price`
end
) as `CategoryB Price`

FROM   `books`

GROUP BY `model`,`name`

However, I am not getting the wanted result.

I hope this crappy example gets my point across.

Thank you

Correct your sample data to express your intention. – Hamlet Hakobyan Aug 28 '14 at 19:28 — Hamlet Hakobyan, Aug 28 '14 at 19:28
Turning rows into columns will always be ugly. :\ – Mr. Llama Aug 28 '14 at 20:07 — Mr. Llama, Aug 28 '14 at 20:07

score 2 · Accepted Answer · answered Aug 28 '14 at 19:28

2

You need an aggregation function. I think max() will work:

SELECT `model`, `name`,
        max(case when `category` = 'CategoryA' then `price` end) as `CategoryA Price`,
        max(case when `category` = 'CategoryB' then `price` end) as `CategoryB Price`,
FROM   `books`
GROUP BY `model`, `name`;

answered Aug 28 '14 at 19:28

Gordon Linoff

1,242,037
58
646
786

I am testing this quickly and apparently it's doing what I want. Is max() save to use? – Bastien Aug 28 '14 at 19:32
@Bastien . . . Assuming you have no duplicates, then it is fine. You have have duplicates, then you need to figure out what you want to do with them -- `max()`, `avg()`, `group_concat()` or whatever. – Gordon Linoff Aug 28 '14 at 20:08

score 0 · Answer 2 · edited May 23 '17 at 12:27

I think that what you need is a pivot table. Since MySQL does not have an instruction to do that, you'll have to build it "by hand"... but it's not as hard as it sounds:

-- First you need to build the column list.
-- The "CASE ... END" expression will filter the data for each column
-- I use "max()" as an example; use whatever aggregate function you need.
select
  group_concat(distinct
    concat(
      'max(case when category = ', del_productID, ' then price end) ',
      'as `category_', category, '` '
    )
  )
into @sql
from books;

-- Now build the full SELECT statement
set @sql = concat('SELECT model, name, ', @sql, ' from books group by model, name');


-- OPTIONAL: Check the SELECT statement you've just built
select @sql;

-- Prepare a statement using the SELECT statement built above
prepare stmt from @sql;
execute stmt;

-- When you are done, be sure to dealocate the prepared statement
deallocate prepare stmt;

Please see here and here for some examples.

If the content's of the category column does not change (i.e. your categories are already there and there won't be more), Gordon Linoff's answer will be enough. But if there can be more categories over time, pivot tables are the way to go.

I have only 5 categories possible. His answer is working so far. I need to test it with more data. Thanks anyways! — Bastien, Aug 28 '14 at 19:37

score 0 · Answer 3 · answered Aug 28 '14 at 19:36

I think you can do this with a self join, assuming you don't have an unreasonable number of categories.

select
  book.model, book.name, A.price, B.price, C.price
from
  books as book
  left join books as A
    on book.model = A.model 
    and book.name = A.name
    and A.category = 'categoryA'
  left join books as B
    on book.model = B.model 
    and book.name = B.name
    and B.category = 'categoryB'
  left join books as C
    on book.model = C.model 
    and book.name = C.name
    and C.category = 'categoryC'

LEFT JOIN vs IF vs CASE vs SUBSELECTS

3 Answers3