Need help on where statement in SQL query

SQL Query assistance needed.

SELECT        Daneandata.*, hdl_masterClaim.claimRemark, hdl_masterClaim.masterClaimID, hdl_masterClaim.medinfoReferralCode, hdl_masterClaim.medinfoCompany, hdl_masterClaim.accession_no,
                         hdl_masterClaim.medinfoApptno
INTO      Daneandata2
FROM            Daneandata LEFT OUTER JOIN
                         hdl_masterClaim ON Daneandata.FillerOrderNumber = hdl_masterClaim.claimRemark
WHERE interfaceID IN ('CRRMR','CRCRAIG')

I need to adjust the query so that WHERE interfaceID is not in ('CRRMR','CRCRAIG') to use in ('rmr_rmr','cr_craig')
DaneanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
WHERE (interfaceID IN ('CRRMR','CRCRAIG')) OR (interfaceID NOT IN ('CRRMR','CRCRAIG')  AND interfaceID IN ('rmr_rmr','cr_craig'))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaneanAuthor Commented:
I get both lines one for the ('CRRMR','CRCRAIG') and the ('RMR_RMR','CR_CRAIG').

If the 'crrmr','crcraig') line does not exist, then I want the query to pick up the 'rmr_rmr','cr_craig') line.

I don't want the query to pick up both lines.


thanks.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should do..
; WITH CTE AS (
SELECT Daneandata.*, hdl_masterClaim.claimRemark, hdl_masterClaim.masterClaimID, hdl_masterClaim.medinfoReferralCode, hdl_masterClaim.medinfoCompany
, hdl_masterClaim.accession_no,hdl_masterClaim.medinfoApptno
, CASE WHEN interfaceID IN ('CRRMR','CRCRAIG') THEN 1 WHEN interfaceID IN ('rmr_rmr','cr_craig') THEN 2 END Flag
INTO Daneandata2
FROM Daneandata
LEFT OUTER JOIN hdl_masterClaim ON Daneandata.FillerOrderNumber = hdl_masterClaim.claimRemark
)
SELECT * 
FROM cte
WHERE Flag = 1
UNION 
SELECT * 
FROM cte t1
WHERE Flag = 2
and not exists (select * from cte t2 where t1.unique_columns = t2.unique_columns and t2.Flag = 1)

Open in new window

0
als315Commented:
Are you sure in correct answer? My answer give you wrong result.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.