How to use SQL LEFT JOIN in Salesforce Marketing Cloud

Question

I have questions around SQL join.

I have 2 tables customer and events One customer can have optin/attend/Cancel multiple events. One customer can have Confirmed/planning/Cancel multiple Orders.

now I am fetching the events, Order, Product details in a target DE with customer details.

    SELECT Acc.*,
    CASE
        WHEN Eve.Status__c ='In Progress' and Eve.Subject ='Event'  THEN 1
        ELSE 0
    END as [HasBookedEvent],


    CASE
        WHEN Eve.Status__c ='Completed' and Eve.Subject ='Event' THEN 1
        ELSE 0
    END as [HasCompletedEvent],


    CASE
        WHEN (Ord.Status='Confirmed' OR Ord.Status='planning') AND Ord.Type='Fleet' and Prod.Model__c='P2' THEN 1
        ELSE 0
    END as [HasActive_P2_FleetOrder],
    CASE
        WHEN (Ord.Status='Confirmed' OR Ord.Status='planning') AND Ord.Type IN('Order','configured','Remarketed') and Prod.Model__c='P2' THEN 1
        ELSE 0
    END as [HasActive_P2_RetailOrder],
    CASE
        WHEN (Ord.Status='Delivered' OR Ord.Status='Partially') AND Ord.Type IN('Order','configured','Remarketed') and Prod.Model__c='P2' THEN 1
        ELSE 0
    END as [HasDelivered_P2]


    FROM [Account_Salesforce] Acc
    LEFT JOIN [Event_Salesforce] Eve ON Eve.AccountId = Acc.Id
    LEFT JOIN [Order_Salesforce] Ord ON Ord.AccountId= Acc.Id
    LEFT JOIN [Product_Salesforce] Prod ON Prod.Id= Ord.Product__c

One of the customer has two events, one is Inprogress and other one is completed. In this case 'HasCompletedEvent' is becoming '1' but not HasBookedEvent.

Same way One of the customer has two Orders(confirmed and Planning). In this case 'HasActive_P2_FleetOrder' should become '1' but it is not happening.

These problem arises only when customer has more then one rows in event/order table.

Do you know why ? How can I make both values 1 when customer has multiple rows in events/Order using CASE statement.

Target DE fields are CID, Cemail,HasBookedEvent,HasCompletedEvent,HasActive_P2_FleetOrder,HasActive_P2_RetailOrder,HasDelivered_P2

Thanks

Answer 1

Please try below SQL.

SELECT Acc.Id
    ,MAX(CASE 
            WHEN Eve.Status__c = 'In Progress'
                AND Eve.Subject = 'Event'
                THEN 1
            ELSE 0
            END) AS [HasBookedEvent]
    ,MAX(CASE 
            WHEN Eve.Status__c = 'Completed'
                AND Eve.Subject = 'Event'
                THEN 1
            ELSE 0
            END) AS [HasCompletedEvent]
    ,MAX(CASE 
            WHEN (
                    Ord.STATUS = 'Confirmed'
                    OR Ord.STATUS = 'planning'
                    )
                AND Ord.Type = 'Fleet'
                AND Prod.Model__c = 'P2'
                THEN 1
            ELSE 0
            END) AS [HasActive_P2_FleetOrder]
    ,MAX(CASE 
            WHEN (
                    Ord.STATUS = 'Confirmed'
                    OR Ord.STATUS = 'planning'
                    )
                AND Ord.Type IN (
                    'Order'
                    ,'configured'
                    ,'Remarketed'
                    )
                AND Prod.Model__c = 'P2'
                THEN 1
            ELSE 0
            END) AS [HasActive_P2_RetailOrder]
    ,MAX(CASE 
            WHEN (
                    Ord.STATUS = 'Delivered'
                    OR Ord.STATUS = 'Partially'
                    )
                AND Ord.Type IN (
                    'Order'
                    ,'configured'
                    ,'Remarketed'
                    )
                AND Prod.Model__c = 'P2'
                THEN 1
            ELSE 0
            END) AS [HasDelivered_P2]
FROM [Account_Salesforce] Acc
LEFT JOIN [Event_Salesforce] Eve ON Eve.AccountId = Acc.Id
LEFT JOIN [Order_Salesforce] Ord ON Ord.AccountId = Acc.Id
LEFT JOIN [Product_Salesforce] Prod ON Prod.Id = Ord.Product__c
GROUP BY Acc.Id