Using left join and inner join in the same query

Below is my query using a left join that works as expected. What I want to do is add another table filter this query ever further but having trouble doing so. I will call this new table table_3 and want to add where table_3.rwykey = runways_updatable.rwykey. Any help would be very much appreciated.

SELECT * FROM RUNWAYS_UPDATABLE LEFT JOIN TURN_UPDATABLE ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY WHERE RUNWAYS_UPDATABLE.ICAO = 'ICAO' AND (RUNWAYS_UPDATABLE.TORA > 4000 OR LDA > 0) AND (TURN_UPDATABLE.AIRLINE_CODE IS NULL OR TURN_UPDATABLE.AIRLINE_CODE = '' OR TURN_UPDATABLE.AIRLINE_CODE = '') 

'*************EDIT To CLARIFY ***************** Here is the other statement that inner join i would like to use and I would like to combine these 2 statements.

SELECT * FROM RUNWAYS_UPDATABLE A, RUNWAYS_TABLE B WHERE A.RWYKEY = B.RWYKEY 

'***What I have so far as advice taken below, but getting syntax error

 SELECT RUNWAYS_UPDATABLE.*, TURN_UPDATABLE.*, AIRPORT_RUNWAYS_SELECTED.* FROM RUNWAYS_UPDATABLE INNER JOIN AIRPORT_RUNWAYS_SELECTED ON RUNWAYS_UPDATABLE.RWYKEY = AIRPORT_RUNWAYS_SELECTED.RWYKEY LEFT JOIN TURN_UPDATABLE ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY 

NOTE: If i comment out the inner join and leave the left join or vice versa, it works but when I have both of joins in the query, thats when im getting the syntax error.

7 Answers

I always come across this question when searching for how to make LEFT JOIN depend on a further INNER JOIN. Here is an example for what I am searching when I am searching for "using LEFT JOIN and INNER JOIN in the same query":

SELECT * FROM foo f1 LEFT JOIN (bar b1 INNER JOIN baz b2 ON b2.id = b1.baz_id ) ON b1.id = f1.bar_id 

In this example, b1 will only be included if b2 is also found.

Remember that filtering a right-side table in left join should be done in join itself.

select * from table1 left join table2 on table1.FK_table2 = table2.id and table2.class = 'HIGH' 

I finally figured it out. Thanks for all your help!!!

SELECT * FROM (AIRPORT_RUNWAYS_SELECTED INNER JOIN RUNWAYS_UPDATABLE ON AIRPORT_RUNWAYS_SELECTED.RWYKEY = RUNWAYS_UPDATABLE.RWYKEY) LEFT JOIN TURN_UPDATABLE ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY 

Add your INNER_JOIN before your LEFT JOIN:

 SELECT * FROM runways_updatable ru INNER JOIN table_3 t3 ON ru.rwykey = t3.rwykey LEFT JOIN turn_updatable tu ON ru.rwykey = tu.rwykey AND (tu.airline_code IS NULL OR tu.airline_code = '' OR tu.airline_code = '') WHERE ru.icao = 'ICAO' AND (ru.tora > 4000 OR ru.lda > 0) 

If you LEFT JOIN before your INNER JOIN, then you will not get results from table_3 if there is no matching row in turn_updatable. It's possible this is what you want, but since your join condition for table_3 only references runways_updatable, I would assume that you want a result from table_3, even if there isn't a matching row in turn_updatable.

EDIT:

As @NikolaMarkovinović pointed out, you should filter your LEFT JOIN in the join condition itself, as you see above. Otherwise, you will not get results from the left-side table (runways_updatable) if that condition isn't met in the right-side table (turn_updatable).

EDIT 2: OP mentioned this is actually Access, and not MySQL

In Access, perhaps it's a difference in the table aliases. Try this instead:

 SELECT [ru].*, [tu].*, [ars].* FROM [runways_updatable] AS [ru] INNER JOIN [airport_runways_selected] AS [ars] ON [ru].rwykey = [ars].rwykey LEFT JOIN [turn_updatable] AS [tu] ON [ru].rwykey = [tu].rwykey AND ([tu].airline_code IS NULL OR [tu].airline_code = '' OR [tu].airline_code = '') WHERE [ru].icao = 'ICAO' AND ([ru].tora > 4000 OR [ru].lda > 0) 

If it is just an inner join that you want to add, then do this. You can add as many joins as you want in the same query. Please update your answer if this is not what you want, though

 SELECT * FROM RUNWAYS_UPDATABLE LEFT JOIN TURN_UPDATABLE ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY INNER JOIN table_3 ON table_3.rwykey = runways_updatable.rwykey WHERE RUNWAYS_UPDATABLE.ICAO = 'ICAO' AND (RUNWAYS_UPDATABLE.TORA > 4000 OR LDA > 0) AND (TURN_UPDATABLE.AIRLINE_CODE IS NULL OR TURN_UPDATABLE.AIRLINE_CODE = '' OR TURN_UPDATABLE.AIRLINE_CODE = '') 

I am not really sure what you want. But maybe something like this:

SELECT RUNWAYS_UPDATABLE.*, TURN_UPDATABLE.* FROM RUNWAYS_UPDATABLE JOIN table_3 ON table_3.rwykey = runways_updatable.rwykey LEFT JOIN TURN_UPDATABLE ON RUNWAYS_UPDATABLE.RWYKEY = TURN_UPDATABLE.RWYKEY WHERE RUNWAYS_UPDATABLE.ICAO = 'ICAO' AND (RUNWAYS_UPDATABLE.TORA > 4000 OR LDA > 0) AND (TURN_UPDATABLE.AIRLINE_CODE IS NULL OR TURN_UPDATABLE.AIRLINE_CODE = '' OR TURN_UPDATABLE.AIRLINE_CODE = '') 

For Postgres, query planner does not guarantee order of execution of join. To Guarantee one can use @Gajus solution but the problem arises if there are Where condition for inner join table's column(s). Either one would to require to carefully add the where clauses in the respective Join condition or otherwise it is better to use subquery the inner join part, and left join the output.