SQL join and not include-CodePudding

I have a table called Exclude:

And a base table called Base:

I want to do a join between these 2 tables which matches on YEAR, but where Base.[BRAND] does not include any values of Exclude.[BRAND]. So in this example that would leave me with ID 3, 4 and 6 only because ID 3 and 4 join to Exclude on [YEAR] = 2023 but exclude where BRAND IN ('A' and 'B'). Similarly ID 6 join to Exclude on [YEAR] = 2024 but exclude where BRAND = 'D'

I've achieved it but don't want to use EXCEPT is that possible at all? Was thinking maybe using APPLY or something but not sure if that'd work either:

WITH EXCLUDE AS
(
SELECT 2023 AS YEAR, 'A' AS BRAND
UNION ALL
SELECT 2023 AS YEAR, 'B' AS BRAND
UNION ALL
SELECT 2024 AS YEAR, 'C' AS BRAND
UNION ALL
SELECT 2024 AS YEAR, 'D' AS BRAND
)

, DATA AS
(
select 1 as ID, 2023 AS YEAR, 'A' AS BRAND
UNION ALL
SELECT 2 AS ID, 2023 AS YEAR, 'B' AS BRAND
UNION ALL
SELECT 3 AS ID, 2023 AS YEAR, 'C' AS BRAND
UNION ALL
SELECT 4 AS ID, 2023 AS YEAR, 'D' AS BRAND
UNION ALL
SELECT 5 AS ID, 2024 AS YEAR, 'D' AS BRAND
UNION ALL
SELECT 6 AS ID, 2024 AS YEAR, 'F' AS BRAND
)                   

SELECT D.* FROM DATA D
EXCEPT
SELECT D.* FROM DATA D
INNER JOIN EXCLUDE E ON D.YEAR = E.YEAR
                    AND D.BRAND = E.BRAND

CodePudding user response：

Your description is not completely clear, however I think you're just looking for a not exists query

select * 
from data d
where not exists (
  select * from exclude e
  where e.brand = d.brand and e.year = d.year
);

This returns IDs 3,4,6

CodePudding user response：

There are different ways to accomplish this. One way is to use a left join to the exclude table as :

SELECT *
FROM Base B
LEFT JOIN Exclude E
  ON B.year = E.year
     AND B.Brand = E.Brand
WHERE E.BRAND IS NULL