How to select rows from table1 which are not in table2 according to FieldX in table2 - but relevant for each different value of Field2 in table 2?

Question

This is for MS SQL Server 2016.

I have Table1 which contains an ID and a Name column (of layouts to populate a html dropdown box).

This dropdown (stores its ID value into Field1 of table2) should only list items which are not already used in rows in field1 of Table2.

The query for this alone would be solved with this SO answer here: How to select all records from one table that do not exist in another table?)

But I have 1 additional requirement:

Within Table2, I have a 2nd column (holds the ClubID - it is also represented by a dropdown, populated from a 3rd table with ID and name) which allows the user to select a club. Now the query which populates Field1 in Table2 should only show items from Table1 which do not yet exist for the chosen Club within Table2 (Field2 will be defined first by the user in order to show the correct items in Field1).

How to do this? (I am unfortunately no SQL Server expert and this is a bit too high for me).

I already have tried the following query and some variants of it to solve it but this didn't work:

SELECT 
    t1.TacMasterLayoutId, t1.TacMasterLayoutName.Name
FROM 
    Table1 t1 
LEFT JOIN 
    Table2 t2 ON t2.TacMasterLayoutId = t1.TacMasterLayoutId
WHERE 
    t2.TacMasterLayoutId IS NULL 
    AND T2.ChainAndSingleRessortId = <FilterId>

(FilterId contains the ID of the selected club).

As requested, some sample data:

Table1: (Layouts)

ID          Name
 1          CoverLetter
 2          AccountingReceipt
 3          IdlePeriodConfirmation
 4          Contract
 5          BonusPeriodConfirmation

Table3: (Clubs)

ID          Name
 1          Club1
 2          Club2

Table2: (Club-Layouts - used to hold all layouts which have been assigned to the Clubs)

ID          Club-ID           Layout-ID
 1          1                 1
 2          2                 1
 3          1                 2
 4          2                 4

Desired output:

Case 1: When Selected Club-ID=1:

(These Layout-IDs are already used within Club-Layouts:)

ID          Club-ID           Layout-ID
 1                1           1
 3                1           2

Therefore I need the following output from the query:

ID         Name
 3          IdlePeriodConfirmation
 4          Contract
 5          BonusPeriodConfirmation

Case 2: When Selected Club-ID=2:

(These Layout-IDs are already used within Club-Layouts:)

ID          Club-ID           Layout-ID
 2          2                 1
 4          2                 4

Therefore I need the following output from the query:

ID         Name
 2          AccountingReceipt
 3          IdlePeriodConfirmation
 5          BonusPeriodConfirmation

Hope this clarifies the situation.

Answer 1

do inner join between table2 and table3 and then do left join with this outcome with table 1

 with cte AS
(select t3.ID,t2.ClubID,t2.LayoutID
 from Table3 t3 
join Table2 t2 on t3.ID=t2.ClubID
where t2.ClubID=1 -- you can change this condition as you need
) select t1.id,t1.name from table1 t1
 left join cte on t1.ID=cte.LayoutID
 where cte.LayoutID is null

Demo in Fiddle

id  name
3   IdlePeriodConfirmation
4   Contract
5   BonusPeriodConfirmation

Answer 2

Try something like this...

select c.id as clubid, c.name as clubname, l.id as layoutid, l.name as layoutname
from layouts l
cross join clubs c
left outer join clublayouts cl
   on cl.layoutid = l.id
   and cl.clubid = c.id
where cl.id is null
/* and if you want the list for a single club add... */ and c.id = <some club id>

Answer 3

Thanks for all your replies.

In the meantime, I solved it like this:

SELECT t1.TacMasterLayoutId, t1.TacMasterLayoutName
FROM Table1 t1 LEFT JOIN (select * from Table2 where T2.ChainAndSingleRessortId = <FilterId>) t2 ON t2.TacMasterLayoutId = t1.TacMasterLayoutId
WHERE t2.TacMasterLayoutId IS NULL
ORDER BY t1.TacMasterLayoutName