SQL query for Joining three tables with condition

Question

I have three tables: vehicle, addressrole, address. I have two tasks,

1. I have to find the number of records from address role table based on one of the date column in the vehicle table. I am able to get the expected resulted using inner join between vehicle and addresssrole table.

2. Based on the above results I have to find the records from addressrole table which are not available in the address table. This is the query I am using:

SELECT addressrole.*,
       address.*
FROM addressrole
  LEFT JOIN address ON addressrole.ID = address.FK_ADDRESSROLE_ID
  INNER JOIN vehicle
          ON vehicle.ID = addressrole.FK_EVN_ID
         AND creationdate > '2018-03-01'

The above query results all the records from address and addressrole tables based on the inner join from vehicle and addressrole table.

Is there any way to get only the unmatched records from addressrole table based on the following inner join?

Answer 1

Is this what you want?

SELECT ar.*, a.*
FROM addressrole ar INNER JOIN
     vehicle v
     ON vehicle.ID = ar.FK_EVN_ID AND
        ?.creationdate > '2018-03-01' LEFT JOIN
     address a
     ON ar.ID = a.FK_ADDRESSROLE_ID 
WHERE a.FK_ADDRESSROLE_ID IS NULL;