T SQL WHERE Clause

Question

Hiya Guys,

I'm trying to develop some code which pulls latest information only.

What it is when a user goes into a form there a subtable and everytime they change something it creates a new row in the colum called type12_OriginalNoteID which puts its own unique number in. Another field called type12_OriginalNoteID keeps the same number - which keeps track of what the orginal number was before any changes were made. I do have some code which does pull the latest information but it does not pull anything if the user has not made any changes to the form - and thats because the type12_OriginalNoteID is null.

The code is as follows :-

WHERE ea.type12_NoteID IN
  (SELECT TOP 1 ea.type12_NoteID 
     FROM UserAssessv1aidsadaptations ea1  
     WHERE ea.type12_NoteID =   ea1.type12_OriginalNoteID 
     ORDER BY ea.type12_UpdatedDate DESC)

An example of the data is as follows :-

type12_note ID         12
type12_OriginalNoteID  NULL 
type12_UpdatedDate     11/03/2010 

What would be the solution to show the information if no one has made any changes to the subtable? - adding an if statement to run if type12_OriginalNoteID is null??

Answer 1

What you need, is to join the two tables using a LEFT JOIN. So the record data from the main table will be still there, but the fields coming from your second table (= subtable) would be null. Your statement should look something like:

SELECT TOP 1 t1.type12_NoteID
FROM t1 LEFT JOIN t2 
ON t1.type12_NoteID = t2.type12_OriginalNoteID
ORDER BY t1.type12_UpdatedDate DESC