CODEWITHSUNDEEP
sql
Soniya
Soniya

Reputation: 231

Fetching Records present in one table but not in other based on multiple conditions

There are Two table

A(UCID,Create_Date, Av_ID,Phone_No)

B(UCID,Create_Date, Av_ID,Phone_No)

I need to fetch the records that are present in A but not in B based on UCID first, after that it may be possible that UCID is not captured in B(UCID will be null in that case) so fetch records present in A not in B , based on rest of three columns,(Create_Date, Av_ID,Phone_No)..

WHAT I HAVE DONE SO FAR IS

SELECT A.* INTO #TEMP FROM A LEFT JOIN B WHERE B.UCID IS NULL
SELECT A.* FROM #temp JOIN B on A.CREATE_DATE= B.CREATE_DATE WHERE A.PHONE_NO=B.PHONE_NO AND A.AV_ID=B.AV_ID

Since there are a number of records so it is taking around 8 minutes to execute, So if there is any way to do the same using single join or without using temp table..please help

Upvotes: 0

Views: 33

Answers (1)

Ivan Burlutskiy
Ivan Burlutskiy

Reputation: 1623

Single query

SELECT A.* 
FROM A 
    LEFT JOIN B on (A.UCID = B.UCID) or 
        (B.UCID IS NULL AND A.CREATE_DATE= B.CREATE_DATE AND A.PHONE_NO=B.PHONE_NO AND A.AV_ID=B.AV_ID)
WHERE B.UCID IS NULL

Upvotes: 1

Related Questions