SQL Question - What is wrong with my WHERE clause

Question

It's been a few years since I had to use SQL and I had to write a query – but I got results that I didn't expect. I've spent so many hours looking at it that I'm sure I'm missing something obvious but, when I run this below, my query is returning RateCode values other than what is in my WHERE statement.

What am I doing wrong? I feel like its a basic enough query (and I'm starting to panic that I've lost my skill)

SELECT
    cs.Acct AS Site,
    cs.Addr1 AS Address,
    cs.City,
    cs.State,
    m.TypeDescr AS Service,
    se.Quantity,
    se.SvcCode,
    se.SvcDescr,
    sf.FreqDescr AS Frequency,
    se.SvcId, 
    se.StartDate,
    se.EndDate,
    sr.ReasonDescr AS Reason,
    svr.RateCode,  
    svr.StartDate AS RateStartDate,
    svr.EndDate AS RateEndDate

FROM
    wiSvcEquipment AS se
    LEFT JOIN wiCustSite    AS cs  ON cs.SiteId    = se.SiteId
    LEFT JOIN wiMaterial    AS m   ON m.MatSubType = se.MatSubType
    LEFT JOIN wiSvcReason   AS sr  ON sr.ReasonID  = se.ReasonID
    LEFT JOIN wiSvcVendRate AS svr ON svr.SvcId    = se.SvcID
    LEFT JOIN wiSvcCustRate as scr ON scr.RateCode = se.RateCode
    LEFT JOIN wiSvcFreq     AS sf  ON sf.FreqID    = se.FreqID

WHERE
    se.CustId = ‘013714’
    OR
    se.CustId = ‘014831’
    AND 
    svr.RateCode IN (
        ‘CHGCMBHAUL’, ‘CHGHAUFLAT’, ‘CHGHAUL2’, ‘CHGHAUL3’,
        ‘CHGHAULCOM’, ‘CHGHAULFE’, ‘CHGHAULING’, ‘CHGHAULMIN’,
        ‘CHGVCMBHAU’, ‘CHGVHAUFLT’, ‘CHGVHAULFE’, ‘CHGVHAULNG’,
        ‘CHGWKDHAUL’, ‘CLIHAUFLAT’, ‘CLIHAULFE’, ‘CLIHAULING’
    ) ;

Answer 1

if se.CustId = ‘013714’ clause is satisfied then no matter what the retcode is this query will return the rows.

Is your intention to use below condition:

WHERE
    (se.CustId = ‘013714’
    OR
    se.CustId = ‘014831’)
    AND 
    svr.RateCode IN (
        ‘CHGCMBHAUL’, ‘CHGHAUFLAT’, ‘CHGHAUL2’, ‘CHGHAUL3’,
        ‘CHGHAULCOM’, ‘CHGHAULFE’, ‘CHGHAULING’, ‘CHGHAULMIN’,
        ‘CHGVCMBHAU’, ‘CHGVHAUFLT’, ‘CHGVHAULFE’, ‘CHGVHAULNG’,
        ‘CHGWKDHAUL’, ‘CLIHAUFLAT’, ‘CLIHAULFE’, ‘CLIHAULING’
    ) ;

Answer 2

Change your WHERE to use IN for both columns:

WHERE
    se.CustId IN ( '013714', '014831' )
    AND 
    svr.RateCode IN (
        'CHGCMBHAUL', 'CHGHAUFLAT', 'CHGHAUL2', 'CHGHAUL3',
        'CHGHAULCOM', 'CHGHAULFE', 'CHGHAULING', 'CHGHAULMIN',
        'CHGVCMBHAU', 'CHGVHAUFLT', 'CHGVHAULFE', 'CHGVHAULNG',
        'CHGWKDHAUL', 'CLIHAUFLAT', 'CLIHAULFE', 'CLIHAULING'
    );

Or use an explicit logical group:

WHERE
    (
        se.CustId = '013714'
        OR
        se.CustId = '014831'
    )
    AND 
    svr.RateCode IN (
        'CHGCMBHAUL', 'CHGHAUFLAT', 'CHGHAUL2', 'CHGHAUL3',
        'CHGHAULCOM', 'CHGHAULFE', 'CHGHAULING', 'CHGHAULMIN',
        'CHGVCMBHAU', 'CHGVHAUFLT', 'CHGVHAULFE', 'CHGVHAULNG',
        'CHGWKDHAUL', 'CLIHAUFLAT', 'CLIHAULFE', 'CLIHAULING'
    );

The two clauses above are strictly identical: in SQL the x IN ( a, b, c ) operator is just syntactic sugar for a series of ( x = a OR x = b OR x = c ) comparison.

Answer 3

did you mean

(se.CustId = ‘013714’ OR se.CustId = ‘014831’)

AND has precedence over OR

Answer 4

I can see three things:

1. You need parentheses -- or better yet -- IN for the first condition.
2. conditions on all but the first table in a series of LEFT JOINs should be in the ON clause.
3. Your strings have smart quotes, but they should be "straight" single quotes.

So:

SELECT . . .
FROM wiSvcEquipment se
     wiCustSite cs 
     ON cs.SiteId = se.SiteId LEFT JOIN
     wiMaterial m  
     ON m.MatSubType = se.MatSubType LEFT JOIN
     wiSvcReason sr 
     ON sr.ReasonID = se.ReasonID AND
            svr.RateCode IN ('CHGCMBHAUL', 'CHGHAUFLAT', 'CHGHAUL2', 'CHGHAUL3',
                             'CHGHAULCOM', 'CHGHAULFE', 'CHGHAULING', 'CHGHAULMIN’,
                             'CHGVCMBHAU', 'CHGVHAUFLT', 'CHGVHAULFE', 'CHGVHAULNG',
                             'CHGWKDHAUL', 'CLIHAUFLA’, 'CLIHAULFE', 'CLIHAULING'
                            ) LEFT JOIN

        LEFT JOIN
     wiSvcVendRate svr
     ON svr.SvcId = se.SvcID LEFT JOIN
     wiSvcCustRate scr
     ON scr.RateCode = se.RateCode LEFT JOIN
     wiSvcFreq sf 
     ON sf.FreqID = se.FreqID
WHERE se.CustId IN ('013714', '014831')