Using left join and inner join in the same query

sql ms-access left-join inner-join
Will picture Will · Mar 13, 2012 · Viewed 93.2k times · Source

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 *****************

Answer

Nikola Markovinović picture Nikola Markovinović · Mar 13, 2012

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'

Related questions

Is it possible to use subquery in join condition in Access?

In postgresql I can use subquery in join condition SELECT * FROM table1 LEFT JOIN table2 ON table1.id1 = (SELECT id2 FROM table2 LIMIT 1); But when I try to use it in Access SELECT * FROM table1 LEFT JOIN table2 ON table1.…

Read More
How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More