CODEWITHSUNDEEP
mysqlsqlms-accessms-access-2007
user1000068
user1000068

Reputation: 23

finding the courses not taking in Staff table ? using SQL

I have two tables The First one contains information about staff who take courses the first table which is named Staff

The second table information about all courses available and it must taking by staff second table which named Course

I want quereu SQL to find the remaining course for every staff

for example :-

Rami intro. Java

Rami into. to Orcad

Rami PIC

Rami intro. to C++

Rayan Intro. Flash

Rayan Intro to c++

Rayan PIC

Rayan Intro. to Photoshop

Rayan Into. to Orcad

.

.

.

. . . . . and so on for all staff

Upvotes: 2

Views: 154

Answers (3)

HansUp
HansUp

Reputation: 97101

With these 3 tables ...

Courses:

CourseName
intro. Flash
intro. Java
intro. to Photoshop
into. To Orcad
intro. to c++
PIC

Staff:

sname
Khalid
Rami
Rayan

StaffCourses:

sname   Trainingcourse
Khalid  into. To Orcad
Rami    intro. Flash
Rami    intro. to Photoshop
Rayan   intro. Java

... this query ...

SELECT
    xjoin.sname,
    xjoin.CourseName
FROM
    [
        SELECT Courses.CourseName, Staff.sname FROM Courses, Staff
    ]. AS xjoin
    LEFT JOIN StaffCourses
    ON
            (xjoin.CourseName = StaffCourses.Trainingcourse)
        AND (xjoin.sname = StaffCourses.sname)
WHERE
    (((StaffCourses.Trainingcourse) Is Null))
ORDER BY
    xjoin.sname,
    xjoin.CourseName;

... produces this result set:

sname  CourseName
Khalid intro. Flash
Khalid intro. Java
Khalid intro. to c++
Khalid intro. to Photoshop
Khalid PIC
Rami   into. To Orcad
Rami   intro. Java
Rami   intro. to c++
Rami   PIC
Rayan  into. To Orcad
Rayan  intro. Flash
Rayan  intro. to c++
Rayan  intro. to Photoshop
Rayan  PIC

Upvotes: 1

Dylan Smith
Dylan Smith

Reputation: 22245

I'll assume you have a Staff table with 1 record per staff member, and a courses table with 1 record per course. And the first table in your post I'll call the CourseRegistrations table.

SELECT Staff.Name, Courses.CourseName 
FROM Courses CROSS JOIN Staff
WHERE NOT EXISTS (SELECT * 
                  FROM CourseRegistrations 
                  WHERE Name = Staff.Name 
                  AND TrainingCourseName = Courses.CourseName)

If you're not familiar with CROSS JOIN's what this is doing is finding all possible combinations of staff and courses, then in the WHERE clause it filters out all combination that already exist in your registration table.

Upvotes: 2

lanan
lanan

Reputation: 2752

Didn't test! But it might work:

SELECT staff.Name, courses.CourseName 
FROM courses LEFT JOIN staff
             ON (staff.TrainingCourse = courses.CourseName)
WHERE TrainingCourse IS NULL

Upvotes: 2

Related Questions