Select unique records from a join?

[jedimastermopar]

Hi,
I have two tables, both tables contain a matching JobID.
Some records in the job table are not present in the joblocation table.
I need to Select only those records.

I thought I could just do a left join on the two tables with a not equal to where clause?

SELECT
job.JobLocation1,
job.City,
job.Province,
job.SiteName1,
job.Code,
job.JobID,
joblocation.JobID
FROM soileng.joblocation joblocation
LEFT OUTER JOIN soileng.job job ON joblocation.JobID = job.JobID
WHERE job.JobID != joblocation.JobID limit 100


Any ideas on how to get this to work?

[guelphdad]

put the table with the missing rows on the right by naming it second in the join. then search for NULLS in that table.


SELECT
job.JobLocation1,
job.City,
job.Province,
job.SiteName1,
job.Code,
job.JobID,
joblocation.JobID
FROM
soileng.job
LEFT OUTER JOIN
soileng.joblocation
ON job.JobID= joblocation.JobID
WHERE joblocation.JobID IS NULL


small note, you don't need to give your tables an alias if you are giving them the same alias name as their already existing name. you don't need

soileng.joblocation joblocation

as you are assigning an alias of joblocation to a table already called joblocation.

[jedimastermopar]

Ahh very cool, thanks.