Let's say I have two tables:

Flights looks like:

and Weather:

where the names of the columns correspond to values in two of the columns in the Flights table.

Currently, I want to reference the column corresponding to Flights.dep_apt_code in Weather to create a table like:

but I haven't been able to figure it out. I'm not the biggest expert on advanced queries, so even if someone can just point me to resources that may help me figure this out, it would be greatly appreciated.

I tried something like:

SELECT
    F.date,
    F.flight_num,
    F.dep_apt_code as dep,
    F.arr_apt_code as arr,
    W.F.dep as weather
FROM Flights as F JOIN Weather as W
WHERE F.date = W.date;

but obviously that doesn't work, I just don't know syntactically how to do it.

How does one reference a column using a value from another table?

CodePudding user response：

SQL requires all identifiers are fixed in your expressions at the time the query is parsed. But you could do a CASE expression like this:

SELECT
    F.date,
    F.flight_num,
    F.dep_apt_code as dep,
    F.arr_apt_code as arr,
    CASE F.dep_apt_code 
      WHEN 'ATL' THEN W.ATL 
      WHEN 'IAD' THEN W.IAD 
      WHEN 'JFK' THEN W.JFK
      WHEN 'SFO' THEN W.SFO 
    END AS dep_weather
FROM Flights AS F JOIN Weather AS W ON F.date = W.date;

The comment above says that you should normalize your structure. This means to store the weather per city on individual rows instead of in columns on one row.

Then you can get the value by joining to the row that matches both the date and the airport code:

SELECT
    F.date,
    F.flight_num,
    F.dep_apt_code as dep,
    F.arr_apt_code as arr,
    W.weather AS dep_weather
FROM Flights AS F JOIN Weather AS W ON F.date = W.date AND F.dep_apt_code = W.apt_code;