CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Matt Hall
Matt Hall

Reputation: 2412

Including records that are missing in another dataset

I'm trying to do something along the lines of a WHERE NOT EXISTS or WHERE NOT IN, but am struggling with the syntax.

This is the results set I've got so far, let's called it PlanActStaff:

SELECT 
    CTE_PlanStaff.RegisterID,
    CTE_PlanStaff.TT_ActivityDate,
    CTE_PlanStaff.TT_ActivityTime,
    CTE_PlanStaff.TT_StaffID,
    CTE_ActStaff.ActStaffID
FROM
    CTE_PlanStaff
INNER JOIN
    CTE_ActStaff
ON
    CTE_PlanStaff.RegisterID = CTE_ActStaff.RegisterID
AND
    CTE_PlanStaff.TT_ActivityDate = CTE_ActStaff.ActSessionDate
AND
    CTE_PlanStaff.TT_ActivityTime = CTE_ActStaff.ActSessionStartTime

For each record in this result set I want the query to go back and check my CTE_PlanStaff CTE to see whether the ActStaffID exists for the current combination of RegisterID, TT_ActivityDate and TT_ActivityTime in CTE_PlanStaff.

So for example, if a record being checked in PlanActStaff looked like this:

| RegisterID | TT_ActivityDate | TT_ActivityTime | TT_StaffID | ActStaffID |
|------------|-----------------|-----------------|------------|------------|
|   98688    |    2016-01-04   |      11:20      |    2453    |    2067    |

CTE_PlanStaff would then be filtered on the same combination of RegisterID, TT_ActivityDate and TT_ActivityTime, so CTE_PlanStaff might then have these records to compare:

| RegisterID | TT_ActivityDate | TT_ActivityTime | TT_StaffID |
|------------|-----------------|-----------------|------------|
|   98688    |    2016-01-04   |      11:20      |    2500    |
|   98688    |    2016-01-04   |      11:20      |    2453    |

I'd then like the ActStaffID value of 2067 checked against each TT_StaffID in the filtered CTE_PlanStaff and if the ActStaffID isn't listed to leave that record showing in my PlanActStaff query.

I've tried to adapt PlanActStaff as follows...

SELECT *
FROM
    (
        SELECT 
            CTE_PlanStaff.RegisterID,
            CTE_PlanStaff.TT_ActivityDate,
            CTE_PlanStaff.TT_ActivityTime,
            CTE_PlanStaff.TT_StaffID,
            CTE_ActStaff.ActStaffID
        FROM
            CTE_PlanStaff
        INNER JOIN
            CTE_ActStaff
        ON
            CTE_PlanStaff.RegisterID = CTE_ActStaff.RegisterID
        AND
            CTE_PlanStaff.TT_ActivityDate = CTE_ActStaff.ActSessionDate
        AND
            CTE_PlanStaff.TT_ActivityTime = CTE_ActStaff.ActSessionStartTime
    ) PlanActStaff
WHERE
    ActStaffID NOT IN (
        SELECT *
        FROM
            CTE_PlanStaff
        WHERE
            CTE_PlanStaff.RegisterID = PlanActStaff.RegisterID
        AND
            CTE_PlanStaff.TT_ActivityDate = PlanActStaff.TT_ActivityDate
        AND
            CTE_PlanStaff.TT_ActivityTime = PlanActStaff.TT_ActivityTime
    )

...but I get the following error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I started looking at WHERE NOT EXISTS, but I can't figure out how to both filter CTE_PlanStaff against the current record and then check the staffID fields.

Upvotes: 0

Views: 40

Answers (1)

Maha Khairy
Maha Khairy

Reputation: 352

WHERE

ActStaffID NOT IN (
    SELECT *
    FROM
        CTE_PlanStaff
    WHERE
        CTE_PlanStaff.RegisterID = PlanActStaff.RegisterID
    AND
        CTE_PlanStaff.TT_ActivityDate = PlanActStaff.TT_ActivityDate
    AND
        CTE_PlanStaff.TT_ActivityTime = PlanActStaff.TT_ActivityTime
)

you should select only one field to be compared against 'ActStaffID'

Upvotes: 1

Related Questions