CODEWITHSUNDEEP
mysql
Tim Holum
Tim Holum

Reputation: 737

Accessing parent column info in a sub query

I am trying to write a sub query to have in a larger query, The subquery I am writting needs to get a.workorder_id from the main column

This one works ( Notice x.workorder_id = '805' )

SELECT a.* , 
( SELECT SUM( maxes ) qty 
  FROM  ( 
     SELECT MAX( qty ) maxes 
     FROM `rework_line` x 
     WHERE x.workorder_id = '805' 
     GROUP BY x.size 
  ) as tba 
) tmpsum 
FROM rework_line a 
WHERE a.workorder_id = '805' 
GROUP BY a.workorder_id

Yet when I change x.workorder_id = a.workorder_id it no longer works and tells me

#1054 - Unknown column 'a.workorder_id' in 'where clause'

SELECT a.* , 
( SELECT SUM( maxes ) 
  qty FROM  ( 
     SELECT MAX( qty ) maxes 
     FROM `mtborah_rework_line` x 
     WHERE x.workorder_id = a.workorder_id 
     GROUP BY x.size 
  ) as tba 
) tmpsum 
FROM mtborah_rework_line a 
WHERE a.workorder_id = '805' 
GROUP BY a.workorder_id

I am sure it is something simple I forgot, but in all my googing I can not find out how to do it, I also tryed asking in the mysql room on freenode, and all they would say is to rewrite it as a join, which is not possible due to the query I am having it in If I re-join to this table it will through off my counts and sum's

Upvotes: 4

Views: 6930

Answers (1)

Taryn
Taryn

Reputation: 247870

Maybe I am missing something, but why don't you rewrite this using a subquery in a join instead of the correlated subquery:

SELECT a.*, qty
FROM mtborah_rework_line a 
LEFT JOIN
(
  SELECT SUM(maxes) qty, workorder_id
  FROM
  (
    SELECT MAX( qty ) maxes, x.workorder_id
    FROM `mtborah_rework_line` x 
    GROUP BY x.size, x.workorder_id
  ) m
  GROUP BY workorder_id
) q
  on a.workorder_id = q.workorder_id
WHERE a.workorder_id = '805';

Or even this way:

SELECT a.*, SUM(maxes) qty
FROM mtborah_rework_line a 
LEFT JOIN
(
  SELECT MAX(qty) maxes, x.workorder_id
  FROM `mtborah_rework_line` x 
  GROUP BY x.size, x.workorder_id
) q
  on a.workorder_id = q.workorder_id
WHERE a.workorder_id = '805' 
GROUP BY a.workorder_id;

Upvotes: 5

Related Questions