Get related tables not contains result-CodePudding

I have a DesignGroup table as:

 -------------------------------------- ---------- 
|            DesignGroupId             |   Name   |
 -------------------------------------- ---------- 
| 3A81C1FF-442F-4291-B8E2-7079D80920CF | Design 1 |
| 3238F4C6-7BA7-4B3F-9383-17702B0D1CC3 | Design 2 |
 -------------------------------------- ----------

Each DesignGroup can have multiple customers, so I have a table DesignGroupCustomers as:

 -------------------------------------- -------------------------------------- ------------- 
|        DesignGroupCustomerId         |          DesignGroupId (FK)          | CustomerKey |
 -------------------------------------- -------------------------------------- ------------- 
| D0828677-F295-46F7-BB85-65888D5A48B7 | 3A81C1FF-442F-4291-B8E2-7079D80920CF |          10 |
| 10C01BB9-1DDB-4DB4-BEC4-9539E030BF68 | 3A81C1FF-442F-4291-B8E2-7079D80920CF |          20 |
| F88C9F66-C0D9-EB11-8481-5CF9DDF6DC87 | 3238F4C6-7BA7-4B3F-9383-17702B0D1CC3 |          10 |
 -------------------------------------- -------------------------------------- -------------

Each customer have a CustomerType as, customerTable:

 ------------- ------------- 
| CustomerKey | CustTypeKey |
 ------------- ------------- 
|          10 |           2 |
|          20 |           1 |
 ------------- -------------

That I want to achieve is to get only this statement:

return only the DesignGroup who not have a customer with custTypeKey = 1

In this case it should return Design 2 because it does not have customer with custTypeKey = 1

I was thinking about CTE usage but I just have not idea how to get the desire result:

;WITH CTE
    AS (SELECT
             [DG].[DesignGroupId]
            , ROW_NUMBER() OVER(PARTITION BY [DesignGroupCustomer]) AS [RN]
             FROM [DesignGroup] AS [DG]
                 INNER JOIN [DesignGroupCustomer] AS [DGC] ON [DG].[DesignGroupId] = [DGC].[DesignGroupId]
                 INNER JOIN [Customer] AS [C] ON [DGC].[CustomerKey] = [C].[CustomerKey]
                 INNER JOIN [CustomerType] AS [CT] ON [C].[CustTypeKey] = [CT].[CustTypeKey])
    SELECT
          [DesignGroupId]
          FROM [CTE] -- WHERE CustomerType NOT CONTAINS (1)

CodePudding user response：

WITH temp AS (
    SELECT DISTINCT
        dgc.DesignGroupId AS DesignGroupId
    FROM DesignGroupCustomers dgc
    INNER JOIN customerTable ct
        ON dgc.CustomerKey = ct.CustomerKey
    WHERE ct.CustTypeKey = 1
)
SELECT
    DesignGroupId
FROM DesignGroup
WHERE DesignGroupId NOT IN (
    SELECT
        DesignGroupId
    FROM temp
)

Firstly, you can get all designgroups having CustTypeKey =1 and then get all other designgroups using NOT IN. Please let me know if you face any issues

CodePudding user response：

You can use a subquery to return the design groups which have this customer type key of 1 and then LEFT JOIN the subquery on the design table and filter down to results that have a DesignGroupId of null (any design group that isn't included in the dataset of the subquery)

SELECT d.[DesignGroupId]
FROM [DesignGroup] AS d
LEFT JOIN
(
    SELECT dgc.[DesignGroupId]
    FROM [DesignGroupCustomer] AS dgc 
        ON dgc.[DesignGroupId] = d.[DesignGroupId]
    INNER JOIN [Customer] AS c 
        ON c.[CustomerKey] = dgc.[CustomerKey]
    WHERE c.[CustTypeKey] = 1
    GROUP BY dgc.[DesignGroupId]
 ) x
     ON x.[DesignGroupId] = d.[DesignGroupId]
 WHERE x.[DesignGroupId] IS NULL