Vfrdtyky

I have the following tables:

Stores:

StoreID | Name

1       | Store1

2       | Store2

3       | Store3



EmID | StoreID

1    | 1

2    | 1

3    | 1

1    | 2

3    | 2

Employee:

EmID | Employee | Important

1    | Cashier  | 1

2    | Manager  | 1

3    | Guard    | 0

I need a query to return StoreID and EmID where Employee is important (Important = 1) and the store and employee are not connected. Basically, the result should be:

StoreID | EmId

--------+-------

2       | 2

3       | 1

3       | 2

I have tried joins, outer joins / apply-es, except, cte, temporary tables, but still haven't found the answer.

Can someone help me with the code, or at least point me in the right direction?
Any idea will be very much appreciated.

Thanks.

You use a cross join to get the set of all possible employee/store combinations, and a left join to then remove the combinations that exist in the join table¹:

declare @Stores table (StoreID int, Name char(6))

insert into @Stores (StoreID,Name) values

(1,'Store1'),

(2,'Store2'),

(3,'Store3')



declare @Employees table (EmID int, Employee varchar(8), Important bit)

insert into @Employees (EmID,Employee,Important) values

(1,'Cashier',1),

(2,'Manager',1),

(3,'Guard'  ,0)



declare @Staffing table (EmID int, StoreID int)

insert into @Staffing (EmID,StoreID) values

(1,1),

(2,1),

(3,1),

(1,2),

(3,2)



select

    *

from

    @Stores s

        cross join

    @Employees e

        left join

    @Staffing st

        on

            s.StoreID = st.StoreID and

            e.EmID = st.EmID

where

    e.Important = 1 and

    st.EmID is null

Results:

StoreID     Name   EmID        Employee Important EmID        StoreID

----------- ------ ----------- -------- --------- ----------- -----------

3           Store3 1           Cashier  1         NULL        NULL

2           Store2 2           Manager  1         NULL        NULL

3           Store3 2           Manager  1         NULL        NULL

¹The one I've named Staffing and you didn't name in the question. Note also (for future questions) that my presentation of the sample data takes up approximately as much space as yours in the question, provides the data types, and is a runnable script.

Please use Cross join followed by Left join and filter on IMP and StoreID null.

    create table #Stores

(storeID int, Name varchar(100))



create table #ES

(empid int,storeID int)



create table #E

(eid int,employee varchar(100), imp int)



insert into #stores values(

1,'Store1'),

(2,'Store2'),

(3,'Store3')



insert into #ES values(

1,1),(2,1),(3,1),(1,2),(3,2)



insert into #E values

(1,'Cashier',1),

(2,'Manager', 1),

(3,'Guard',0)



select * from #Stores

select * from #ES

select * from #E



select #stores.storeid,#E.eid from #Stores

cross join #E

LEFT join #ES

on #ES.storeid = #Stores.storeid

and #E.eid = #ES.empid

where #E.imp = 1

and #ES.storeID is null

Try this query.

I assumed the table name of the "Employee" is dbo.Employee and table name of "Stores" is dbo.Stores and the intermediate table is "dbo.EmpStore"

SELECT S.StoreID, E.EmID

FROM dbo.Stores S

CROSS JOIN  dbo.Employees E

LEFT JOIN dbo.EmpStore ES ON ES.EmID = E.EmID AND ES.StoreID = S.StoreID

WHERE  E.Important=1 AND ES.EmID IS NULL