SQL Statement - Excluding certain data

Question

I'm still in the learning phase of SQL statements and I'm hoping someone out there can help.

I have a many-to-many database base relationship.

The table Department can have multiple Jobs associated with it and and Jobs can be related to multiple Departments. So I have this basic relationship type.

Job.ID (one-to-many) Jobs.JobID
Jobs.DepartmentID (many-to-one) Department.ID

What I'm trying to do is get a list of Jobs that aren't already associated with a department.

tbl=Job
ID  Job     Active
1   10-3242  Yes
2   12-3902  Yes
3   12-3898  Yes

tbl=Jobs
ID  DepartmentID    JobID
1        3            1
2        3            2

tbl=Department
ID  Department
1   Administration
2   Sales
3   Production

Query:

string sql = "SELECT Job FROM (Job " +
    "INNER JOIN Jobs ON Job.ID = Jobs.JobID) " +
    "INNER JOIN Department ON Jobs.DepartmentID = Department.ID " +
    "WHERE Department.Department <> 'Production'";

I'm expecting the job code 12-3898 to be returned but obviously I'm forgetting something.

Any assistance would be great. Cheers.

Answer 1

You can use a LEFT JOIN. The LEFT JOIN keyword returns all rows from the left table with the matching rows in the right table. The result is NULL in the right side if there is no match. Since you want the jobs without a matching department, you can check if the joined DepartmentID is NULL:

SELECT Job.Job
FROM   Job LEFT JOIN Jobs ON Job.ID = Jobs.JobID
WHERE  Jobs.DepartmentID IS NULL;

Checkout this demo. Let me know if it works.