Mysql: unable to join a subquery

Question

I would really appreciate any feedback. My code is below and the error code is 1054. 'subq1.IATA' in the 'on' clause is unknown.

SELECT     SUM(CASE 
           WHEN ABS(TZ_NUM_DEP-TZ_NUM_ARR)>=2 THEN 1 ELSE 0
           END AS Total)
FROM ontime AS o1
INNER JOIN (SELECT SUBSTRING_INDEX(a1.Name,' Airport',1) AS Departure,
       CASE 
       WHEN a1.Timezone='America/New_York (Eastern)' THEN 1
       WHEN a1.TimeZone='America/Chicago (Central)' THEN 2
       WHEN a1.Timezone='America/Denver and America/Phoenix (Mountain)' THEN 3
       WHEN a1.Timezone='America/Los_Angeles (Pacific)' THEN 4
       END AS TZ_NUM_Dep,
       SUBSTRING_INDEX(a2.Name,' Airport',1) AS Arrival,
       CASE 
       WHEN a2.Timezone='America/New_York (Eastern)' THEN 1
       WHEN a2.TimeZone='America/Chicago (Central)' THEN 2
       WHEN a2.Timezone='America/Denver and America/Phoenix (Mountain)' THEN 3
       WHEN a2.Timezone='America/Los_Angeles (Pacific)' THEN 4
       ELSE NULL
       END AS TZ_NUM_Arr
FROM ontime AS o2
LEFT JOIN airports AS a1
ON o2.Origin=a1.IATA
LEFT JOIN airports AS a2
ON o2.Dest=a2.IATA
WHERE a1.Country='United States' AND a2.Country='United States' AND Cancelled=0 AND Diverted=0) subq1
ON o1.Origin=subq1.IATA;

AntoineB · Answer 1 · 2018-08-04T15:21:18.447

In your subquery you never select IATA, which means that it is not present in the "table" subq1. Add it to the subquery:

SELECT     SUM(CASE 
           WHEN ABS(TZ_NUM_DEP-TZ_NUM_ARR)>=2 THEN 1 ELSE 0
           END) AS Total
FROM ontime AS o1
INNER JOIN (SELECT SUBSTRING_INDEX(a1.Name,' Airport',1) AS Departure,
       a1.IATA AS IATA, -- Add this line
       CASE 
       WHEN a1.Timezone='America/New_York (Eastern)' THEN 1
       WHEN a1.TimeZone='America/Chicago (Central)' THEN 2
       WHEN a1.Timezone='America/Denver and America/Phoenix (Mountain)' THEN 3
       WHEN a1.Timezone='America/Los_Angeles (Pacific)' THEN 4
       END AS TZ_NUM_Dep,
       SUBSTRING_INDEX(a2.Name,' Airport',1) AS Arrival,
       CASE 
       WHEN a2.Timezone='America/New_York (Eastern)' THEN 1
       WHEN a2.TimeZone='America/Chicago (Central)' THEN 2
       WHEN a2.Timezone='America/Denver and America/Phoenix (Mountain)' THEN 3
       WHEN a2.Timezone='America/Los_Angeles (Pacific)' THEN 4
       ELSE NULL
       END AS TZ_NUM_Arr
FROM ontime AS o2
LEFT JOIN airports AS a1
ON o2.Origin=a1.IATA
LEFT JOIN airports AS a2
ON o2.Dest=a2.IATA
WHERE a1.Country='United States' AND a2.Country='United States' AND Cancelled=0 AND Diverted=0) subq1
ON o1.Origin=subq1.IATA;

Thanks so much! but now I got a new error code which suggests error in line 3. It says I don't have closing parenthesis for end as total. any thoughts? — user9864272, Aug 04 '18 at 14:59
Check this answer for the right syntax: https://stackoverflow.com/a/20935337/3876196 — AntoineB, Aug 04 '18 at 15:08

ScaisEdge · Answer 2 · 2018-08-04T14:59:30.877

you have not a column IATA in subquery named subq1 .. try add the column in subquery eg:

AND you have as TOTAL in wrong position

  SELECT     SUM(
            CASE 
             WHEN ABS(TZ_NUM_DEP-TZ_NUM_ARR)>=2 THEN 1 ELSE 0
             END ) AS Total 
  FROM ontime AS o1
  INNER JOIN (
          SELECT a1.IATA, 
          SUBSTRING_INDEX(a1.Name,' Airport',1) AS Departure,
         CASE 
         WHEN a1.Timezone='America/New_York (Eastern)' THEN 1
         WHEN a1.TimeZone='America/Chicago (Central)' THEN 2
         WHEN a1.Timezone='America/Denver and America/Phoenix (Mountain)' THEN 3
         WHEN a1.Timezone='America/Los_Angeles (Pacific)' THEN 4
         END AS TZ_NUM_Dep,
         SUBSTRING_INDEX(a2.Name,' Airport',1) AS Arrival,
         CASE 
         WHEN a2.Timezone='America/New_York (Eastern)' THEN 1
         WHEN a2.TimeZone='America/Chicago (Central)' THEN 2
         WHEN a2.Timezone='America/Denver and America/Phoenix (Mountain)' THEN 3
         WHEN a2.Timezone='America/Los_Angeles (Pacific)' THEN 4
         ELSE NULL
         END AS TZ_NUM_Arr
  FROM ontime AS o2
  LEFT JOIN airports AS a1
  ON o2.Origin=a1.IATA
  LEFT JOIN airports AS a2
  ON o2.Dest=a2.IATA
  WHERE a1.Country='United States' AND a2.Country='United States' AND Cancelled=0 AND Diverted=0) subq1
  ON o1.Origin=subq1.IATA;

Thanks so much! but now I got a new error code which suggests error in line 3. It says I don't have closing parenthesis for end as total. any thoughts? — user9864272, Aug 04 '18 at 14:55
yes .. as total in wrong position .. must be outside sum () answer updated — ScaisEdge, Aug 04 '18 at 14:59

Mysql: unable to join a subquery

2 Answers2