How to Specify the Parent Query Field from Within a Subquery in MySQL

How to specify the parent query field from within a subquery in MySQL?

How about:

$query = "SELECT p1.id, 
                 (SELECT COUNT(1) 
                    FROM post_table p2 
                   WHERE p2.parent_id = p1.id) as num_children
            FROM post_table p1
           WHERE p1.parent_id = 0";

or if you put an alias on the p1.id, you might say:

$query = "SELECT p1.id as p1_id, 
                 (SELECT COUNT(1) 
                    FROM post_table p2 
                   WHERE p2.parent_id = p1.id) as num_children
            FROM post_table p1
           WHERE p1.parent_id = 0";

Get parent column in 3rd-level subquery

As a principle, you want to move the sub-queries in to your FROM clause.

Try something like this...

 SELECT
    c.in_customer_id,
    s.grouped_schedule
 FROM
    tbl_customers   AS c
 LEFT JOIN
 (
    SELECT
       in_customer_id,
       group_concat(the_schedule separator '\r\n') AS grouped_schedule
    FROM
    (
       SELECT
          a.in_customer_id,
          a.in_customer_address_id,
          concat_ws('\n', 'Route: ', s.route_id, 'Interval: ', s.service_interval, 'Week No.: ', s.week_no, 'Weekdays: ', s.weekdays, 'Start Date: ', s.start_date, 'End Date: ', s.end_date, 'Start Time: ', s.start_time, 'End Time: ', s.end_time, '\n') AS the_schedule
       FROM
          tbl_customer_address  AS a
       INNER JOIN
          schedule              AS s
             ON s.service_address_id = a.in_customer_address_id
       WHERE
          s.is_skipped = 0
    )
       AS schedules
    GROUP BY
       in_cusomer_id
)
   AS s
      ON s.in_customer_id = c.in_customer_id

How to use results of subquery in a parent query case statement

You can use a subquery or CTE:

SELECT lh.*, . . .   -- any expressions you want
       (case when last_delivery_id = 1 then 'pending'
             when last_delivery_id = 2 then 'active'
        end) as status
FROM (SELECT l.*,
             (SELECT id
              FROM hauls h
              WHERE h.haul_type_id = 1 AND
                    h.location_id = l.id
              ORDER BY h.h.created_at DESC
              LIMIT 1
             ) AS last_delivery_id,
             (SELECT id
              FROM hauls h
              WHERE h.haul_type_id = 2 AND
                    h.location_id = l.id
              ORDER BY h.created_at DESC
              LIMIT 1
             ) AS last_pickup_id
      FROM locations l
     ) lh

Note the use of table aliases which make the query easier to write and to read. That said, I'm not sure you really need the delivery_id if you just want the string version:

      SELECT l.*,
             (SELECT (case when h.id = 1 then 'pending'
                           when h.id = 2 then 'active'
                      end)
              FROM hauls h
              WHERE h.haul_type_id = 1 AND
                    h.location_id = l.id
              ORDER BY h.created_at DESC
              LIMIT 1
             ) AS status
      FROM locations l

How do I make a reference to a table column from the parent query within the subquery in SQL?

You are quite close. You just need proper table aliases:

SELECT p.person_id as PersonID,
       p.name as PersonName,
       p.parent_person_id as ParentID,
       (SELECT parent.name
        FROM people parent
        WHERE p.parent_person_id = parent.person_id;
       ) as ParentName
FROM people p;

Notes:

• This would more normally be expressed as a LEFT JOIN, but the correlated subquery is also a very reasonable approach.
• Notice that the query is easier to follow with table aliases -- and you need them in this case.
• Don't get in the habit of using single quotes for column names. This often causes confusion and can result in hard to find bugs.
• Your version doesn't work because in the condition people.parent_person_id = people.person_id, people refers to the inner from. You need table aliases to distinguish between the inner and outer references to people.

Use column of parent query in subquery of a subquery

Wow. So many nested subqueries. Instead of nesting queries to the ends of the earth, use JOINs and aggregate your data to calculate what you need. I had to make some guesses about your table structures because you didn't supply them (something that you should always do when posting a database question).

SELECT
    S.id,
    S.type,
    S.subtype,
    S.title,
    S.shortdesc,
    COUNT(DISTINCT SC.id) AS comments,
    AVG(SR.rating) AS rating,
    calls,
    user
FROM
    Storys S  -- Storys isn't the plural of Story, BTW
LEFT OUTER JOIN Story_Comments SC ON SC.parent_id = S.id
LEFT OUTER JOIN Story_Ratings SR ON SR.parent_id = S.id
WHERE
    S.open = 1 AND
    S.modremove = 0 AND
    S.modblock = ''
GROUP BY
    S.id,
    S.type,
    S.subtype,
    S.title,
    S.shortdesc,
    S.calls,
    S.user
ORDER BY
    opening
DESC LIMIT 16;

I don't think that the "* 2 ... / 2" does what you think it does, based on the various parentheses and the rounding might not be right here depending on the data type of your rating column (again, no table structures, so I'm stuck guessing).

I don't have a MySQL server handy, but testing this code on SQL Server (adjusting for difference in the ROUND function) gave the same results as your first query.

MySQL subquery to refer to field in parent query

I think you might consider joining two in line views it might make things eaiser.

SELECT * 
FROM   (SELECT COUNT(*), 
               a2.page_title 
        FROM   ratings a2 
        WHERE  DATE(timestamp) <= '2011-04-24' 
               AND DATE(timestamp) >= '2011-04-17' 
               AND rating >= 1 
               AND rating <= 2 

        GROUP  BY a2.page_title) current 
       JOIN 
        (SELECT a1.page_title, 
                    COUNT(*)       AS rvol, 
                    AVG(a1.rating) AS theavg 
             FROM   ratings a1 
             WHERE  DATE(a1.timestamp) <= '2011-04-17' 
                    AND DATE(a1.a_timestamp) >= '2011-04-11' 
             GROUP  BY a1.page_title 
             HAVING COUNT(*) > 20) morethan20 
         ON current .page_title = morethan20.page_title 

Second Subquery reference to parent-parent query: column not found

As it seems in a normal way not possible to resolve the problem (double nested subquery reference to outer query), i now solved the problem by creating a mysql function with the user id as parameter.

hope this will help other searchers

Sub sub-query can't find joined column in parent select

Thanks to dba.stackexchange.com

link: https://dba.stackexchange.com/questions/126339/subquery-cant-find-column-from-superquerys-join

Answer by ypersillycubeᵀᴹ

---

Here's the answer that was posted there, hopefully someone else will profit as well from this!
The cause of the problem was identified by @Phil in the comments: in the comments:
Probably because it's nested too deep
You have 2 layers of nesting and the reference of table e cannot "see" through these 2 layers in MySQL.

Correlated inline subquery can usually be converted to derived tables and then LEFT joined to the other tables in the FROM clause but they have to be turned into uncorrelated (in MySQL. In other DBMS, you could use a LATERAL join or the similar OUTER APPLY.

A first rewrite to get the job done:

  SELECT 
       CONCAT_WS(
           ', ', count(DISTINCT CONCAT(q.emailaddress, '_', e.id)),
           dv.OneTimeItems
       ) as AllItems,
       e.id,
       LEFT(e.firstname, 1) as voorletter,
       e.lastname
   FROM question q 
   LEFT JOIN employee e ON q.employee_id = e.id 
   LEFT JOIN
       (
          SELECT company_id,
                 GROUP_CONCAT(items SEPARATOR '; ') AS OneTimeItems
          FROM ( 
               SELECT oi.company_id,
                     CONCAT_WS(
                ': ', oi.item_name, SUM(oi.item_amount)
            ) items 
            FROM onetime_item oi 
            WHERE oi.date BETWEEN '2015-12-01'
                              AND LAST_DAY('2015-12-01') 
            GROUP BY oi.company_id, oi.item_name 
        ) resulta
        GROUP BY company_id
    ) AS dv
    ON dv.company_id = e.company_id
WHERE 1=1 
AND YEAR(q.created_at) = '2015'
AND MONTH(q.created_at) = '12' 
GROUP BY e.company_id ;

Test in SQLfiddle.

Unrelated to the issue comments:

There is GROUP BY e.company_id while the select list has e.id, LEFT(e.firstname, 1), e.lastname. All these will give arbitrary result from a (more or less random) employee for each company - or even in extremely rare cases arbitrary results from 2 or 3 different employees! MySQL allowed (before 5.7) such bad use of group by that could cause erroneous results. It has been fixed in 5.7 and the default settings would reject this query.
The condition:

YEAR(created_at) = '2015' AND MONTH(created_at) = '12'

cannot make use of indexes. It's better to rewrite with either BETWEEN if the column is of DATE type of with an inclusive-exclusive range condition, which works flawlessly with any datetime type (DATE, DATETIME, TIMESTAMP) of any precision:

-- use only when the type is DATE only

date BETWEEN '2015-12-01' AND LAST_DAY('2015-12-01')

or:

-- use when the type is DATE, DATETIME or TIMESTAMP

created_at >= '2015-12-01' AND created_at < '2016-01-01' 

Accessing parent column info in a sub query

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;

---

Related Topics