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’
) ;
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’
) ;