Get parent column in 3rd-level subquery

Question

How can I get a column from the top-most parent query in a subquery within a subquery? Do I have to pass it like a variable? Here's my code:

SELECT c.in_customer_id, 
( 
     SELECT
         group_concat(the_schedule separator '\r\n') 
     FROM
     ( 
         SELECT
             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
             schedule s 
         WHERE
             s.service_address_id IN 
             ( 
                 SELECT in_customer_address_id 
                   FROM   tbl_customer_address a2 
                  WHERE  a2.in_customer_id = c.in_customer_id
             ) 
             AND s.is_skipped = '0'
         GROUP BY
             s.service_address_id
     ) a
 )
     AS "Schedule"
 FROM
     tbl_customers c

The response I get is "Error Code: 1054. Unknown column 'c.in_customer_id' in 'where clause'"

you may also try to create a CTE with schedules and join it to customer table, I think it will be easier to read — Mike Twc
– Mike Twc, Commented Nov 6, 2018 at 19:39
@MikeTwc - Except for the MySQL tag... (MySQL 8 has CTEs, but it's still relatively uncommonly used.) — MatBailie
– MatBailie, Commented Nov 7, 2018 at 9:38

MatBailie · Accepted Answer · 2018-11-06 17:13:31Z

1

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

answered Nov 6, 2018 at 17:13

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MatBailie Over a year ago

I'm pretty sure you could actually do it without sub-queries at all, but hopefully this demonstrates a useful pattern for when sub-queries are useful.

MatBailie Over a year ago

Also, here's a demonstration that what you're trying works in MySQL but only one level deep. sqlfiddle.com/#!9/2b519c/2

Daniel Harris Over a year ago

The first subquery returns more than one in_customer_id which results in an error "Error Code: 1241. Operand should contain 1 column(s)".

MatBailie Over a year ago

Are you running this exactly as is, or using it as a sub-query within another query? (I know the sub queries return multiple customers, they're meant to. They're also Rows, not columns. Your error refers to columns, which makes me suspicious as to how your using this.)

Victor Over a year ago

@MatBailie you say that "as a principle", but should it work with 3 level subqueries ? I am asking because it seems that the parent is seen in the second level but not in the third.

Collectives™ on Stack Overflow

Get parent column in 3rd-level subquery

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related