Return everything not in other table? 1

[SQLNewbiexaus]

Iam sure this a simple one, but I still need help please??
I have these 2 script tables but I need to end up with one table which will return all records from the second table that are not in the first, so has been a status 97 but never been a status 5. Not sure if I should be nesting or putting in temp tables as I want to manipulate the data again from there.

Select Distinct
*
From
History (nolock)
Where
StatusCode = 5
And
DocumentType = 'VN'

Select Distinct
*
From
History (nolock)
Where
StatusCode = 97
And
DocumentType = 'VN'

 

[markros]

What two tables you're talking about? Can you show what data do you have and what is your desired output?

PluralSight Learning Library

 

[dhulbert]

SELECT *
FROM  (SELECT * FROM HISTORY WHERE STATUSCODE = 5 AND DOCUMENTTYPE = 'VN') H1
       LEFT OUTER JOIN
      (SELECT * FROM HISTORY WHERE STATUSCODE = 97 AND DOCUMENTTYPE = 'VN') H2
      ON
      H1.KEYFIELD = H2.KEYFIELD
WHERE H2.KEYFIELD IS NULL

I have used H1.KEYFIELD AND H2.KEYFIELD beacuse there are no field names in your example but you should be able to swap them for the right filed nams.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[imex]

Try something like that:

Select Distinct
      * 
From
      History 
Where
      StatusCode = 97 
And
      DocumentType = 'VN'
And
      HistoryID not in 
              (Select Distinct 
                     HistoryID
               From
                     History
               Where
                     StatusCode = 5
               And
                     DocumentType = 'VN')

I hope this help.



Imobiliárias em Suzano