How i can use Join instead of sub-queries for following query?

Question

Greeting, as seen on many post here and there that sub-queries is slower than join...

but i can't find a way to make following query using any join method.. so, i used sub-queries.

can any one tell me how to use join correctly for following case:

table1:

customerID, Name
1, abc
2, xyz
3, qwe
4, zxc
5, asd
and so on

table2:

customerID, Month, OrderNumbers
1, jan, 5
1, feb, 6
2, jan, 8
3, feb, 5
4, mar, 3
and so on..

i need to make report like this:

customer id, name, jan order, feb order, mar order
1, abc, 5, 6, 0
2. xyz, 8, 0, 0
3. qwe, 0, 5, 0
and so on

i am using this query:

select table1.customerID,
       table1.Name,
       (select table2.Month as jan
         where table2.Month = jan),
       (select table2.Month as feb
         where table2.Month = feb),
       (select table2.Month as mar
         where table2.Month = mar)
  from table1

but this not working as it should...

so, how can i achieve that?

you need a pivot table. check this out http://stackoverflow.com/questions/7674786/mysql-pivot-table — Alex M, Dec 12 '15 at 17:42

score 1 · Accepted Answer · answered Dec 12 '15 at 22:36

Your query essentially requires a long to wide reshaping or pivot transformation which can be done with conditional aggregation:

SELECT 
    table1.customerID,
    table1.`name`,      

    SUM(CASE WHEN table2.`Month` = 'jan' THEN table2.`OrderNumbers` END) As 'jan order',
    SUM(CASE WHEN table2.`Month` = 'feb' THEN table2.`OrderNumbers` END) As 'feb order',
    SUM(CASE WHEN table2.`Month` = 'mar' THEN table2.`OrderNumbers` END) As 'mar order',
    SUM(CASE WHEN table2.`Month` = 'apr' THEN table2.`OrderNumbers` END) As 'arp order',
    SUM(CASE WHEN table2.`Month` = 'may' THEN table2.`OrderNumbers` END) As 'may order',
    SUM(CASE WHEN table2.`Month` = 'jun' THEN table2.`OrderNumbers` END) As 'jun order',
    SUM(CASE WHEN table2.`Month` = 'jul' THEN table2.`OrderNumbers` END) As 'jul order',
    SUM(CASE WHEN table2.`Month` = 'aug' THEN table2.`OrderNumbers` END) As 'aug order',   
    SUM(CASE WHEN table2.`Month` = 'sep' THEN table2.`OrderNumbers` END) As 'sep order',
    SUM(CASE WHEN table2.`Month` = 'oct' THEN table2.`OrderNumbers` END) As 'oct order',
    SUM(CASE WHEN table2.`Month` = 'nov' THEN table2.`OrderNumbers` END) As 'nov order',
    SUM(CASE WHEN table2.`Month` = 'dec' THEN table2.`OrderNumbers` END) As 'dec order'

FROM
    table1
LEFT OUTER JOIN 
    tabl2 ON table1.customerID = table2.customerID
GROUP BY 
    table1.customerID,
    table1.`name`

thanks a lot works like charm :). take care, may Allah bless you, have a nice day, bye bye... — Zakir_SZH, Dec 13 '15 at 07:07
sir, another question, how can i add total order field in the same query? can you please help me on that? currently i am loop through all row/array and to do calculation.. but i think that can be done with same sql query? — Zakir_SZH, Dec 13 '15 at 08:37

score 0 · Answer 2 · answered Dec 12 '15 at 17:48

Here's a solution that I have used many times in msql...

select customerID,Name,sum(jan) as jan,sum(feb) as feb from(
select table1.customerID,Name,(case when Month = 'jan' then OrderNumbers else 0 end) as jan, select table1.customerID,Name,(case when Month = 'feb' then OrderNumbers else 0 end) as febfrom table1
left join table2 on table2.customerID = table1.customerID
) as src group by customerID,Name

basically you use a select case to only populate the month with either the sales or 0 which gives you something like

1,abc,5,0,0
1,abc,0,3,0
2,ddd,0,0,5

Then you simply group the result and sum the months.

How i can use Join instead of sub-queries for following query?

2 Answers2