Using left join and inner join in the same query

Question

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.

Ogata

And can you tell us what is the problem? What are you receive and what you expect? – Ambsace

Answer 1

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.

Truncheon

interesting syntax – Draff

Wow, never knew!! – Vinous

cannot thank enough; sitting for the last 2h and about to refactor code when came across your ingenious left/inner join trick - bullseye! – Sunflower

Grateful to have posted this couple of years ago. Keep coming back to it lol – Truncheon

This works but there is performance issue in my case. – Parapodium

Answer 2

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'

Philipphilipa

To clarify, could you also go: where table2.class = 'HIGH' OR table2.class is null; – Tricorn

@robertking No, number of returned rows might be different because table2.class might contain nulls in the first place. Join condition would filter them out, while where condition would include them. Also, while all the rows from table1 will be returned if join condition is used, where condition will exclude rows from table1 which do not have corresponding row in table2 where class is null or class = 'HIGH'. – Sitar

Answer 3

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

Ogata

This helped me enormously. Thanks. You should mark it correct to make it easier to find. – Sophister

Answer 4

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)

Nutritive

Thanks for trying to answer this but your answer did not work. Im getting a syntax error stating: Missing operator in query expression. What i did was replace table_3 with the table_3 and received that error – Ogata

@Will: What version of MySQL are you using? I don't see any syntax errors in any of the answers posted here. – Nutritive

@Will: Access is not MySQL. However, according to the Access 2003 doc, the same syntax should work. What version of Access are you using? – Nutritive

Access 2003, I have made edits to my original question to show what I have so far but im still getting syntax error – Ogata

@Will: I'm not very familiar with the differences between MySQL and Access, but check my last update on this answer and see if it works. – Nutritive

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

Keir

Thanks for trying to answer this but your answer did not work. Im getting a syntax error stating: Missing operator in query expression. – Ogata

Now that you say that this is access, I would bet that this is coming from something else in your setup. Can you try to run the above query directly (as written..no logic from access), or is that what you are trying? – Keir

Answer 6

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

Innis

thanks for trying to answer my question but im getting an error stating: syntax error in FROM clause – Ogata

Try changing SELECT * to SELECT RUNWAYS_UPDATABLE.*, TURN_UPDATABLE.* - the error may be caused by duplicate column names on table_3. – Kaitlynkaitlynn

Nice notice. Updated the answer – Innis

Tried it but still getting syntax error will update what I have so far as to what I have. I took some code out to make it less complicated. – Ogata

Answer 7

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.

Agro