Using Left Join and Inner Join in the Same Query

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.

1

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.

3

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'
2

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
0

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)
5

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 = '') 
2

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 = '') 
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

James H. Sterling
Author

James H. Sterling

James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.