Would I use an OUTER JOIN?

[Nodral]

Morning All

 

I'm having issues with a query I need to write.

 

I have the following 2  tables

 

• 1 - id, employee number, employee name, location.
  
• 2 - id, employee number, course title
  

 

I need to create a query where I can give the location and a course title and the values returned will be all those from the location who have NOT attended the course given. 

 

Any thoughts?

[marcelobm]

probably this will work

 

SELECT table_1.* FROM table_1 LEFT JOIN table_2 ON table_1.emp_number = table_2.emp_number
WHERE location = 'locaction' AND course_title != 'title';

 

But anyways I think you should rethink the way your database is made.

[Nodral]

There are several other tables and columnswhch reference each other which I have omitted for the purpose of this query, and the DB contains several hundred thousand records so a restructure is out of the question.

 

The query you have written will only return values where an employee of the location has attended a course which is not the given one, rather than ALL employees for a certain location which have not attended the course.  I have some employees who have not attended any courses which would not show in your query.

 

This is the same issue I am having.

[marcelobm]

the query i posted will return all employees from a specific location that hasn't attended the specific course, but you can add

 

SELECT table_1.* FROM table_1 LEFT JOIN table_2 ON table_1.emp_number = table_2.emp_number
WHERE location = 'locaction' AND (course_title != 'title' OR course_title IS NULL);

and that should give you what you are looking for.

[kickstart]

Hi

 

I presume there are multiple courses per person, in which case I think the above suggestion will bring back all the other courses people have done

 

I would try:-

 

SELECT table_1.* 
FROM table_1 
LEFT OUTER JOIN table_2 
ON table_1.emp_number = table_2.emp_number AND table_2.course_title = 'title'
WHERE location = 'locaction'
AND course_title IS NULL;

 

This is using a LEFT OUTER JOIN against the courses table, but checking the course title in the ON clause. This way anyone who has done that course will have a row returned from the join with the course title populated while anyone else will have a row returned from the join with the course title being null. The where clause then drops the rows where the course title is not null.

 

All the best

 

Keith

[fenway]

probably this will work

 

SELECT table_1.* FROM table_1 LEFT JOIN table_2 ON table_1.emp_number = table_2.emp_number
WHERE location = 'locaction' AND course_title != 'title';

 

But anyways I think you should rethink the way your database is made.

 

the query i posted will return all employees from a specific location that hasn't attended the specific course, but you can add

 

SELECT table_1.* FROM table_1 LEFT JOIN table_2 ON table_1.emp_number = table_2.emp_number
WHERE location = 'locaction' AND (course_title != 'title' OR course_title IS NULL);

and that should give you what you are looking for.

 

To be clear, neither of the above will work -- however, kickstart's solution will (as usual).