Ключ DISTINCT, но он возвращает дубликаты; Запрос с несколькими JOINS - ошибка в моем выражении WHERE?

Question

Ожидание: я хотел бы получить всех клиентов, которые купили статью из определенной категории (например, дроны) с начала года.

Проблема: я получаю дубликаты; то есть клиент купил снова в другое время (меня это не волнует)

Соотношение таблиц:

• [Master_CustomerData] has_many
  • [Master_OrderHeader] has_many
    • [Master_OrderedArticle] принадлежит_ к
      • [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

Вот мое рабочее решение. Я удалил "отличную", максимизированную "AB.Category" и сгруппировал по тому, что должно быть отличным.

Спасибо за все ответы...

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 

Answer 2

Для даты у вас есть максимальная дата, поэтому не следует выбирать повторяющиеся значения
если вы используете группу, вам не нужно DISTINCT

может быть, вам нужна сумма для того, чтобы вы сделали то же самое (если кто-то заказывает два раза один и тот же товар, у вас есть дублирующиеся строки)

SELECT CD.SubscriberKey AS MCD_SubscriberKey
,   MAX(OH.OrderDate) AS OH_OrderDate
,   MAX(OA.OrderNo) AS OA_OrderNo
,   OA.ArticleNo AS OA_ArticleNo
,   SUM(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 AB.Category = 811000 
AND OA.QuantityCancelled = 0
AND OH.OrderDate > '2018-01-01'

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