using not exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using not exists

Author

Topic

sqlprog
Starting Member

2 Posts

Posted - 2007-08-02 : 11:06:06

I have two tables. One with employee records, the other with a combination of department and occupation used to assign a number to each employee.

How do I find occupation/department combinations in employee table that do not have a number assigned to them in the combo table(that is, have not been entered in table 2?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-02 : 11:40:45

Something like:

SELECT *
FROM Employee E
WHERE NOT EXISTS (
        SELECT *
        FROM DepartmentOccupation DO
        WHERE DO.DepartmentID = E.DepartmentID
            AND DO.OccupationID = E.OccupationID
    )

SELECT E.*
FROM Employee E
    LEFT JOIN DepartmentOccupation DO
        ON E.DepartmentID = DO.DepartmentID
            AND E.OccupationID = DO.OccupationID
WHERE DO.DepartmentID IS NULL

sqlprog
Starting Member

2 Posts

Posted - 2007-08-02 : 14:20:25

Thanks, Ifor, for your quick reply. It works great.

But, my query is a little more involved. The occupation in DepartmentOccupation (DO) table is the parent occupation from occupation table.

So, basically, Employee.OccupationID=Occupation.OccupationID
DO.OccupationID=Occupation.ParentOccupationID

I need to see what occupation/department combos haevn't been added to the DO table yet.

Here is my query below. Unfortunately, it includes some combos that are already there in the DO table. I cannot figure out why. Can yall help?

select distinct Employee.OccupationID, Occupation.ParentOccupationID,
Occ2.OccupationDesc ParentOcc,Occupation.OccupationDesc, Employee.DepartmentID,
Department.DepartmentDescription
from Employee, Occupation, Department, Occupation Occ2
where
Employee.DepartmentID = Department.DepartmentID
and Employee.OccupationID = Occupation.OccupationID
and Occupation.ParentOccupationID = Occ2.OccupationID
and not exists (select 1 from DepartmentOccupation DO
where DO.OccupationID = Occupation.ParentOccupationID
and DO.DepartmentID = Employee.DepartmentID)

Thanks in advance. Stuck for too long on this crazy query!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-03 : 04:54:56

It is difficult to tell what you want without DDL, sample data and expected results.
Maybe the following will help you:

-- To View
SELECT DISTINCT
    E.OccupationID
    ,O.ParentOccupationID
    ,O.OccupationDesc
    ,P.OccupationDesc
    ,E.DepartmentID
    ,D.DepartmentDescription
FROM Employee E
    JOIN Occupation O
        ON E.OccupationID = O.OccupationID
    JOIN Department D
        ON E.DepartmentID = D.DepartmentID
    JOIN Occupation P
        ON O.ParentOccupationID = P.OccupationID
WHERE NOT EXISTS (
        SELECT *
        FROM DepartmentOccupation DO
        WHERE DO.DepartmentID = E.DepartmentID
            AND DO.OccupationID = O.ParentOccupationID
    )

-- To Insert
INSERT INTO DepartmentOccupation (DepartmentID, OccupationID)
SELECT DISTINCT
    ,E.DepartmentID
    ,O.ParentOccupationID
FROM Employee E
    JOIN Occupation O
        ON E.OccupationID = O.OccupationID
WHERE NOT EXISTS (
        SELECT *
        FROM DepartmentOccupation DO
        WHERE DO.DepartmentID = E.DepartmentID
            AND DO.OccupationID = O.ParentOccupationID
    )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources