CODEWITHSUNDEEP
t-sqlsql-server-2012
akaWizzmaster
akaWizzmaster

Reputation: 103

Query returning multiple results. Only expecting 1.

I am trying to determine why I am getting multiple results from this query. I need to make sure I only have 1 result per UserId. 

declare @UserId INT = 1

   SELECT u.UserId,
          cfeLead.ElementName AS 'LeadSource' ,
          rt.ReferralType AS 'MarketingSource' ,
          cfe.ElementName AS 'Corporate'
   FROM    Users u
           LEFT JOIN ReferralType rt ON rt.ReferralTypeID = u.ReferralTypeID
           LEFT JOIN UserCustomFields ucf ON ucf.UserID = u.UserID
           LEFT JOIN CustomFields cf ON cf.CustomFieldID = ucf.CustomFieldID
                                        AND (cf.CustomFieldName = 'Corporate' OR cf.CustomFieldName = 'Corporate Membership')
           LEFT JOIN CustomFieldElements cfe ON cfe.CustomFieldID = cf.CustomFieldID
                                                AND ucf.CustomFieldElementID = cfe.CustomFieldElementID
           LEFT JOIN CustomFields cfLead ON cfLead.CustomFieldID = ucf.CustomFieldID
                                        AND cfLead.CustomFieldName = 'Lead Source'
           LEFT JOIN CustomFieldElements cfeLead ON cfeLead.CustomFieldID = cfLead.CustomFieldID
                                                AND ucf.CustomFieldElementID = cfeLead.CustomFieldElementID
   WHERE   u.UserID = @UserId

My result is showing:

UserId     LeadSource    MarketingSource   Corporate
1          NULL          DirectMail        180 Center
1          Walkin        DirectMail        NULL

I would like to see:

UserId     LeadSource    MarketingSource   Corporate
1          Walkin        DirectMail        180 Center

My assumption is that the Multiple Left Joins are causing this, but I can't seem to track this down. Thank you all in advance.

Upvotes: 0

Views: 142

Answers (3)

akaWizzmaster
akaWizzmaster

Reputation: 103

Okay, so it took a little longer than I wanted it to, but I wanted to post my answer. 

declare @UserId INT = 1

   SELECT u.UserId,
      MAX(cfeLead.ElementName) AS 'LeadSource' ,
      MAX(rt.ReferralType) AS 'MarketingSource' ,
      MAX(cfe.ElementName) AS 'Corporate'
   FROM    Users u
       LEFT JOIN ReferralType rt ON rt.ReferralTypeID = u.ReferralTypeID
       LEFT JOIN UserCustomFields ucf ON ucf.UserID = u.UserID
       LEFT JOIN CustomFields cf ON cf.CustomFieldID = ucf.CustomFieldID
                                    AND (cf.CustomFieldName = 'Corporate' OR cf.CustomFieldName = 'Corporate Membership')
       LEFT JOIN CustomFieldElements cfe ON cfe.CustomFieldID = cf.CustomFieldID
                                            AND ucf.CustomFieldElementID = cfe.CustomFieldElementID
       LEFT JOIN CustomFields cfLead ON cfLead.CustomFieldID = ucf.CustomFieldID
                                    AND cfLead.CustomFieldName = 'Lead Source'
       LEFT JOIN CustomFieldElements cfeLead ON cfeLead.CustomFieldID = cfLead.CustomFieldID
                                            AND ucf.CustomFieldElementID = cfeLead.CustomFieldElementID
   WHERE   u.UserID = @UserId
   GROUP BY u.UserId

Doing this worked like a charm.

Upvotes: 0

YetAnotherSQL
YetAnotherSQL

Reputation: 26

I'm almost certain that the mixing of JOIN predicates with Conditional predicates is the root of your problem. If I'm correct, then the following code ought to work correctly and return only one row:

DECLARE @UserId INT = 1

SELECT u.UserId
,   cfeLead.ElementName AS 'LeadSource' 
,   rt.ReferralType AS 'MarketingSource' 
,   cfe.ElementName AS 'Corporate'
    FROM Users u
    LEFT JOIN ReferralType rt ON rt.ReferralTypeID = u.ReferralTypeID
    LEFT JOIN UserCustomFields ucf ON ucf.UserID = u.UserID
    LEFT JOIN CustomFields cf ON cf.CustomFieldID = ucf.CustomFieldID
    LEFT JOIN CustomFieldElements cfe
        ON cfe.CustomFieldID = cf.CustomFieldID
        AND ucf.CustomFieldElementID = cfe.CustomFieldElementID
    LEFT JOIN CustomFields cfLead ON cfLead.CustomFieldID = ucf.CustomFieldID
    LEFT JOIN CustomFieldElements cfeLead
        ON cfeLead.CustomFieldID = cfLead.CustomFieldID
        AND ucf.CustomFieldElementID = cfeLead.CustomFieldElementID
    WHERE  u.UserID = @UserId
        AND (cf.CustomFieldName = 'Corporate'
            OR cf.CustomFieldName = 'Corporate Membership')
        AND cfLead.CustomFieldName = 'Lead Source'

Upvotes: 1

FLICKER
FLICKER

Reputation: 6683

Use Coalesce

declare @UserId INT = 1

   SELECT distinct u.UserId,
          Coalesce(cfeLead.ElementName, cfe.ElementName) AS 'LeadSource' ,
          rt.ReferralType AS 'MarketingSource' ,
          Coalesce(cfe.ElementName, cfeLead.ElementName) AS 'Corporate'
   FROM    Users u
           LEFT JOIN ReferralType rt ON rt.ReferralTypeID = u.ReferralTypeID
           LEFT JOIN UserCustomFields ucf ON ucf.UserID = u.UserID
           LEFT JOIN CustomFields cf ON cf.CustomFieldID = ucf.CustomFieldID
                                        AND (cf.CustomFieldName = 'Corporate' OR cf.CustomFieldName = 'Corporate Membership')
           LEFT JOIN CustomFieldElements cfe ON cfe.CustomFieldID = cf.CustomFieldID
                                                AND ucf.CustomFieldElementID = cfe.CustomFieldElementID
           LEFT JOIN CustomFields cfLead ON cfLead.CustomFieldID = ucf.CustomFieldID
                                        AND cfLead.CustomFieldName = 'Lead Source'
           LEFT JOIN CustomFieldElements cfeLead ON cfeLead.CustomFieldID = cfLead.CustomFieldID
                                                AND ucf.CustomFieldElementID = cfeLead.CustomFieldElementID
   WHERE   u.UserID = @UserId

when using Left Join and you want UN-NULL values, use Coalesce over all joined tables

Upvotes: 1

Related Questions