DISTINCT key but it returns duplicates; Query with multiple JOINS - Bug in my WHERE statement?

Question

Expectation: I would like to get all customers who bought an article from an specific category (i. e. drones) since the beginning of the year.

Problem: I get duplicates; i. e. the customer purchased again at a different time (I don't care about that)

Relationship of the tables:

• [Master_CustomerData] has_many
  • [Master_OrderHeader] has_many
    • [Master_OrderedArticles] belongs_to
      • [Master_ArticleBase]

---

SELECT
    DISTINCT CD.SubscriberKey AS MCD_SubscriberKey
,   MAX(OH.OrderDate) AS OH_OrderDate
,   MAX(OA.OrderNo) AS OA_OrderNo
,   OA.ArticleNo AS OA_ArticleNo
,   OA.QuantityOrdered AS OA_QuantityOrdered
,   AB.Category AS AB_Category
,   CD.EmailAddress AS EmailAddress
,   CD.EmailLanguage AS EmailLanguage
,   CD.Gender AS Gender
,   CD.FirstName AS FirstName
,   CD.LastName AS LastName
FROM [Master_CustomerData] AS CD

INNER JOIN [Master_OrderHeader] AS OH
ON  CD.SubscriberKey = OH.SubscriberKey

INNER JOIN [Master_OrderedArticles] AS OA
ON OH.OrderNo = OA.OrderNo

INNER JOIN [Master_ArticleBase] AS AB
ON OA.ArticleNo = AB.ArticleNo

WHERE
        /* Category group  */
    AB.Category IN (811000)

AND OA.QuantityCancelled = 0

AND OH.OrderDate > '2018-01-01'

GROUP BY 
    CD.SubscriberKey
,   CD.EmailAddress
,   OA.ArticleNo
,   OA.QuantityOrdered
,   AB.Category    
,   CD.EmailLanguage
,   CD.Gender
,   CD.FirstName
,   CD.LastName 

Answer 1

Here is my working solution. I removed the "distinct", maxed "AB.Category" and grouped by what has to be distinct.

Thx, for all answers...

SELECT
    CD.SubscriberKey AS MCD_SubscriberKey
,   MAX(OH.OrderDate) AS OH_OrderDate
,   MAX(OA.OrderNo) AS OA_OrderNo
,   MAX(AB.Category) AS AB_Category
,   CD.EmailAddress AS EmailAddress
,   CD.EmailLanguage AS EmailLanguage
,   CD.Gender AS Gender
,   CD.FirstName AS FirstName
,   CD.LastName AS LastName
FROM [Master_CustomerData] AS CD

INNER JOIN [Master_OrderHeader] AS OH
ON  CD.SubscriberKey = OH.SubscriberKey

INNER JOIN [Master_OrderedArticles] AS OA
ON OH.OrderNo = OA.OrderNo

INNER JOIN [Master_ArticleBase] AS AB
ON OA.ArticleNo = AB.ArticleNo

WHERE
    /* Category defined: Dornes, cameras, outdoor, consoles */  
    AB.Category IN (
        211000,
        212000,
        ...
        791700
    )

    /* Article not Cancelled */
AND OA.QuantityCancelled = 0

    /* OrderDate */
AND OH.OrderDate > '2017-08-01'

GROUP BY 
    CD.SubscriberKey
,   CD.EmailAddress
,   CD.EmailLanguage
,   CD.Gender
,   CD.FirstName
,   CD.LastName