CODEWITHSUNDEEP
sqlsql-server
Apostrofix
Apostrofix

Reputation: 2180

How to select records which don't exist in another table or have a different status?

I am trying to select records from a temp table based on another temp table which holds their previous statuses (StatusHistory).

So if the record doesn't exist in the status table, then it should be selected. If the status of the record is different than the one in the StatusHistory table, then the record should be selected. Otherwise, if it exists with the same status in the StatusHistory table, then it should be ignored.

I have this SQL but it doesn't seem to be the best solution. Can you please point me to a better way to achieve that assuming that there are thousands of records in the tables? Would it be possible to achieve the same result with a JOIN statement?

SELECT  AI.item
FROM #AllItems AI
WHERE NOT EXISTS ( 
        SELECT * FROM #StatusHistory HS
           WHERE HS.itemId = AI.itemId
                 ) OR NOT AI.itemStatus IN ( SELECT HS.itemStatusHistory
                                             FROM #StatusHistory HS
                                             WHERE HS.itemId = AI.itemId 
                                             AND HS.itemId = AI.itemId )

Upvotes: 0

Views: 56

Answers (1)

Richard
Richard

Reputation: 30618

Yes, you can do this with a LEFT JOIN.

SELECT AI.item
FROM #AllItems AI
    LEFT JOIN #StatusHistory HS ON AI.itemId = HS.itemId
        AND AI.itemStatus = HS.itemStatusHistory
WHERE HS.itemId IS NULL

A better solution, however, is to use NOT EXISTS:

SELECT AI.item
FROM #AllItems AI
WHERE NOT EXISTS
(
   SELECT 1 FROM #StatusHistory SH
   WHERE SH.itemId = AI.itemId
       AND SH.itemStatusHistory = AI.itemStatus
);

As pointed out by Aaron, this usually performs better than a LEFT JOIN.

Upvotes: 5

Related Questions