CODEWITHSUNDEEP
sqlms-accessleft-joininner-join
Will
Will

Reputation: 1164

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.

Upvotes: 48

Views: 171727

Answers (7)

Debasish Mitra
Debasish Mitra

Reputation: 1535

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.

Upvotes: 1

Gajus
Gajus

Reputation: 73738

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.

Upvotes: 110

Will
Will

Reputation: 1164

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

Upvotes: 18

Travesty3
Travesty3

Reputation: 14469

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)

Upvotes: 12

Arion
Arion

Reputation: 31239

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

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

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'

Upvotes: 24

Justin Pihony
Justin Pihony

Reputation: 67065

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

Upvotes: 1

Related Questions