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?
 
als315Connect With a Mentor Commented:
WHERE (interfaceID IN ('CRRMR','CRCRAIG')) OR (interfaceID NOT IN ('CRRMR','CRCRAIG')  AND interfaceID IN ('rmr_rmr','cr_craig'))
0
 
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 & ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.