Filter values of related database-CodePudding

I have four tables

Project 
DesignGroup
DesignGroupCustomer
Customer

The relation is: Project can have multiple DesignGroups and each DesignGroup can have multiple customers

The Customer table has a column called CustomerTypeKey

So I want to create a query that return all DesignGroups who not have CustTypeKey = 7

So I try:

SELECT
      [p].[ProjectKey]
      FROM [project] AS [p]
          INNER JOIN [designgroup] AS [dg] ON [p].[ProjectKey] = [dg].[ProjectKey]
          INNER JOIN [DesignGroupCustomer] AS [dgc] ON [dg].[DesignGroupId] = [dgc].[DesignGroupId]
          INNER JOIN [Customer] AS [c] ON [dgc].[CustomerKey] = [c].[CustomerKey]
      WHERE [c].[CustTypeKey] NOT IN(7)

But this returns all projects, how can I achieve that only get project who not have custTypeKey = 7 on DesignGroupCustomer table?

CodePudding user response：

Using not in is correct in logic, but you had to focus it on the customer itself and not its type:

SELECT [p].[ProjectKey]
FROM 
    [project] AS [p]
    INNER JOIN [designgroup] AS [dg] ON [p].[ProjectKey] = [dg].[ProjectKey]
    INNER JOIN [DesignGroupCustomer] AS [dgc] ON [dg].[DesignGroupId] = [dgc].[DesignGroupId]
WHERE [dgc].[CustomerKey] NOT IN
(
    SELECT CustomerKey 
    FROM [Customer] AS [c] 
    WHERE [c].[CustTypeKey]=7
)