MySQL Join Two Queries Horizontally

Question

I have a query that works correctly to pull a series of targets and total hours worked for company A. I would like to run the exact same query for company B and join them on a common date, which happens to be grouped by week. My current query:

SELECT * FROM (
  SELECT org, date,
         ( SELECT SUM( target ) FROM target WHERE org = "companyA" ) AS companyA_target,
         SUM( hours ) AS companyA_actual
  FROM time_management_system
  WHERE org = "companyA"
  GROUP BY WEEK( date )
  ORDER BY DATE
) q1

LEFT JOIN (
  SELECT org, date,
         ( SELECT SUM( target ) FROM target WHERE org = "companyB" ) AS companyB_target,
         SUM( hours ) AS companyB_actual
  FROM time_management_system
  WHERE org = "companyB"
  GROUP BY WEEK( date )
  ORDER BY DATE
) q2 

ON q1.date = q2.date

The results show all of the dates / information of companyA, however companyB only shows sporadic data. Separately, the two queries will show the exact same set of dates, just with different information in the 'target' and 'actual' columns.

companyA    2012-01-28  105.00  39.00   NULL      NULL          NULL   NULL 
companyA    2012-02-05  105.00  15.00   NULL      NULL          NULL   NULL
companyA    2012-02-13  105.00  60.50   companyB  2012-02-13    97.50  117.50

Any idea why I'm not getting all the information for companyB?

As a side note, would anybody be able to point in the direction of converting each row's week value into a column? With companyA and companyB as the only two rows?

I appreciate all the help! Thanks.

Answer 1

WITH no date apparent in the target table, the summation will be constant across all weeks. So, I have performed a pre-query for only those "org" values of company A and B with a group by. This will ensure only 1 record per "org" so you don't get a Cartesian result.

Then, I am querying the time_management_system ONCE for BOTH companies. Within the field computations, I am applying an IF() to test the company value and apply when correct. The WEEK activity is the same for both in the final result, so I don't have to do separately and join. This also prevents the need of having the date column appear twice. I also don't need to explicitly add the org column names as the final column names reflect that.

SELECT
      WEEK( tms.date ) as GrpWeek,
      IF( tms.org = "companyA", TargetSum.CompTarget, 00000.00 )) as CompanyATarget,
      SUM( IF( tms.org = "companyA", tms.hours, 0000.00 )) as CompanyAHours,
      IF( tms.org = "companyB", TargetSum.CompTarget, 00000.00 )) as CompanyBTarget,
      SUM( IF( tms.org = "companyB", tms.hours, 000.00 )) as CompanyBHours
   from
      Time_Management_System tms
         JOIN ( select 
                      t.org,
                      SUM( t.target ) as CompTarget
                   from 
                      Target T
                   where
                      t.org in ( "companyA", "companyB" ) 
                   group by
                      t.org ) as TargetSums
            ON tms.org = TargetSums.org
   where 
      tms.org in ( "companyA", "companyB" )
   group by
      WEEK( tms.date )
   order by
      WEEK( tms.date )

Answer 2

Both of your subqueries are wrong.

Either you want this:

SELECT 
org,
WEEK(date),
( SELECT SUM( target ) FROM target WHERE org = "companyB" ) AS companyB_target,
SUM( hours ) AS companyB_actual
FROM time_management_system
WHERE org = "companyB"
GROUP BY WEEK( date )

Or else you want this:

SELECT 
org,
date,
( SELECT SUM( target ) FROM target WHERE org = "companyB" ) AS companyB_target,
SUM( hours ) AS companyB_actual
FROM time_management_system
WHERE org = "companyB"
GROUP BY date

The way you are doing it now is not correctly formed SQL. In pretty much any other database your query would fail immediately with an error. MySQL is more lax and runs the query but gives indeterminate results.

• GROUP BY and HAVING with Hidden Columns