Reputation: 105081

Getting those records that have a match of all records in another table

Within the realm of this problem I have 3 entities:

User
Position
License

Then I have two relational (many-to-many) tables:

PositionLicense - this one connects Position with License ie. which licenses are required for a particular position
UserLicense - this one connects User with License ie. which licenses a particular user has. But with an additional complexity: user licenses have validity date range (ValidFrom and ValidTo)

The problem

These are input variables:

UserID that identifiers a particular User
RangeFrom defines the lower date range limit
RangeTo defines the upper date range limit

What I need to get? For a particular user (and date range) I need to get a list of positions that this particular user can work at. The problem is that user must have at least all licenses required by every matching position.

I'm having huge problems writing a SQL query to get this list.

If at all possible I would like to do this using a single SQL query (can have additional CTEs of course). If you can convince me that doing it in several queries would be more efficient I'm willing to listen in.

Some workable data

Copy and runs this script. 3 users, 3 positions, 6 licenses. Mark and John should have a match but not Jane.

create table [User] (
    UserID int identity not null
        primary key,
    Name nvarchar(100) not null
)
go

create table Position (
    PositionID int identity not null
        primary key,
    Name nvarchar(100) not null
)
go

create table License (
    LicenseID int identity not null
        primary key,
    Name nvarchar(100) not null
)
go

create table UserLicense (
    UserID int not null
        references [User](UserID),
    LicenseID int not null
        references License(LicenseID),
    ValidFrom date not null,
    ValidTo date not null,
    check (ValidFrom < ValidTo),
    primary key (UserID, LicenseID)
)
go

create table PositionLicense (
    PositionID int not null
        references Position(PositionID),
    LicenseID int not null
        references License(LicenseID),
    primary key (PositionID, LicenseID)
)
go

insert [User] (Name) values ('Mark the mechanic');
insert [User] (Name) values ('John the pilot');
insert [User] (Name) values ('Jane only has arts PhD but not medical.');

insert Position (Name) values ('Mechanic');
insert Position (Name) values ('Pilot');
insert Position (Name) values ('Doctor');

insert License (Name) values ('Mecha');
insert License (Name) values ('Flying');
insert License (Name) values ('Medicine');
insert License (Name) values ('PhD');
insert License (Name) values ('Phycho');
insert License (Name) values ('Arts');

insert PositionLicense (PositionID, LicenseID) values (1, 1);
insert PositionLicense (PositionID, LicenseID) values (2, 2);
insert PositionLicense (PositionID, LicenseID) values (2, 5);
insert PositionLicense (PositionID, LicenseID) values (3, 3);
insert PositionLicense (PositionID, LicenseID) values (3, 4);

insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (1, 1, '20110101', '20120101');
insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (2, 2, '20110101', '20120101');
insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (2, 5, '20110101', '20120101');
insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (3, 4, '20110101', '20120101');
insert UserLicense (UserID, LicenseID, ValidFrom, ValidTo) values (3, 6, '20110101', '20120101');

Resulting solution

I've setup my resulting solution based on accepted answer which provides the most simplified solution to this problem. If you'd like to play with the query just hit edit/clone (whether you're logged in or not). What can be changed:

three variables:
1. two variable to set date range (@From and @To)
2. user ID (@User)
you can toggle commented code in the first CTE to switch code between fully overlapping user licenses or partially overlapping ones.

Upvotes: 1

Answers (3)

Cade Roux

Reputation: 89741

Runnable Version Here

WITH PositionRequirements AS (
    SELECT p.PositionID, COUNT(*) AS LicenseCt
    FROM #Position AS p
    INNER JOIN #PositionLicense AS posl
        ON posl.PositionID = p.PositionID
    GROUP BY p.PositionID
)
,Satisfied AS (
    SELECT u.UserID, posl.PositionID, COUNT(*) AS LicenseCt
    FROM #User AS u
    INNER JOIN #UserLicense AS perl
        ON perl.UserID = u.UserID
        -- AND @Date BETWEEN perl.ValidFrom AND perl.ValidTo
        AND '20110101' BETWEEN perl.ValidFrom AND perl.ValidTo
    INNER JOIN #PositionLicense AS posl
        ON posl.LicenseID = perl.LicenseID
    -- WHERE u.UserID = @UserID -- Not strictly necessary, we can go over all people
    GROUP BY u.UserID, posl.PositionID
)
SELECT PositionRequirements.PositionID, Satisfied.UserID
FROM PositionRequirements
INNER JOIN Satisfied
    ON Satisfied.PositionID = PositionRequirements.PositionID
    AND PositionRequirements.LicenseCt = Satisfied.LicenseCt

You could probably turn this into an inline table-valued function parameterized on effective date.

Upvotes: 3

Thomas

Reputation: 64674

Select ...
From User As U
    Cross Join Position As P
Where Exists    (
                Select 1
                From PositionLicense As PL1
                    Join UserLicense As UL1
                        On UL1.LicenseId = PL1.LicenseId
                            And UL1.ValidFrom <= @RangeTo
                            And UL1.ValidTo >= @RangeFrom
                Where PL1.PositionId = P.Id
                    And UL1.UserId = U.Id
                Except
                Select 1
                From PositionLicense As PL2
                    Left Join UserLicense As UL2
                        On UL2.LicenseId = PL2.LicenseId
                            And UL2.ValidFrom <= @RangeTo
                            And UL2.ValidTo >= @RangeFrom
                            And UL2.UserId = U.Id
                Where PL2.PositionId = P.Id
                    And UL2.UserId Is Null
                )

If the requirement is that you want users and positions that are valid across the entire range, that is trickier:

With Calendar As 
    (
    Select @RangeFrom As [Date]
    Union All
    Select DateAdd(d, 1, [Date])
    From Calendar
    Where [Date] <= @RangeTo
    )
Select ...
From User As U
    Cross Join Position As P
Where Exists    (
                Select 1
                From UserLicense As UL1
                    Join PositionLicense As PL1
                        On PL1.LicenseId = UL1.LicenseId
                Where UL1.UserId = U.Id
                    And PL1.PositionId = P.Id
                    And UL1.ValidFrom <= @RangeTo
                    And UL1.ValidTo >= @RangeFrom
                Except
                Select 1
                From Calendar As C1
                    Cross Join User As U1
                    Cross Join PositionLicense As PL1
                Where U1.Id = U.Id
                    And PL1.PositionId = P.Id
                    And Not Exists  (
                                    Select 1
                                    From UserLicense As UL2
                                    Where UL2.LicenseId = PL1.LicenseId
                                        And UL1.UserId = U1.Id
                                        And C1.Date Between UL2.ValidFrom And UL2.ValidTo
                                    )
                )
Option ( MaxRecursion 0 );

Upvotes: 3

Philip Kelley

Reputation: 40359

This makes a number of assumptions (ignores presence of time in the datetime columns, assumes fairly obvious primary keys) and skips the joins to pull in user name, position details, and the like. (And you implied that the user had to hold all the licenses for the full period specified, right?)

SELECT pl.PositionId
 from PositionLicense pl
  left outer join (--  All licenses user has for the entirety (sp?) of the specified date range
                   select LicenseId
                    from UserLicense
                    where UserId = @UserId
                     and @RangeFrom <= ValidFrom
                     and @RangeTo >= ValidTo) li
   on li.LicenseId = pl.LicenseId
 group by pl.PositionId 
 --  Where all licenses required by position are held by user
 having count(pl.LicenseId) = count(li.LicenseId)

No data so I can't debug or test it, but this or something very close to it should do the trick.

Upvotes: 3

Getting those records that have a match of all records in another table

The problem

Some workable data

Resulting solution

Answers (3)

Related Questions