CODEWITHSUNDEEP
mysql
user9864272
user9864272

Reputation: 11

Mysql: unable to join a subquery

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;

Upvotes: 1

Views: 31

Answers (2)

AntoineB
AntoineB

Reputation: 4694

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;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

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;

Upvotes: 0

Related Questions