Writing a SQL statement to link fields from 3 tables.

[shenlingstyle]

Hi, I'm a beginner to databases and querying so hope you can help me out!

I have 4 tables involved: FLIGHT_LEG, FLIGHT_DATE_GEN, ACTIVE_SCENARIO, SCENARIO

The problem is that the FLIGHT_DATE_GEN table doesn't have a scenario field (which I want). Each record should have a corresponding scenario. The table however has a field called FLIGHT_DATE which is unique and also present in the FLIGHT_LEG table.

The FLIGHT_LEG table in turn has a field called MULTI_USER_SESSION_ID, which is not unique but is also present in the ACTIVE_SCENARIO table. This table has a SCENARIO_ID field which is also present in the SCENARIO table.

And finally in the SCENARIO table there is a field called SCENARIO_NAME, which corresponds to each SCENARIO_ID.

I need to extact all the data in the FLIGHT_DATE_GEN table and also have a column that contains the data from the SCENARIO_NAME field.

Hope that wasn't too confusing, how can I write a SQL statement to get this data?

Thanks for reading, and I appreciate any help.

Colin

 

[rtmpxr]

SELECT FLIGHT_DATE_GEN.*,SCENARIO_NAME FROM FLIGHT_DATE_GEN
LEFT JOIN FLIGHT_LEG ON FLIGHT_LEG.FLIGHT.DATE=FLIGHT_DATE_GEN.FLIGHT_DATE
LEFT JOIN ACTIVE_SCENARIO ON ACTIVE_SCENARIO.MULTI_USER_SESSION_ID=FLIGHT_LEG.MULTI_USER_SESSION_ID
LEFT JOIN SCENARIO ON SCENARIO.SCENARIO_ID=ACTIVE_SCENARIO.SCENARIO_ID

 

[shenlingstyle]

Thank you very much.