#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    144
    Rep Power
    13

    SQL Query Pulling NULL value when JOINED with other tables


    Hello and thanks for reading. I seem to be having an issue pulling a date from a field from my Query. All other fields in the Query pull correctly and it doesn't throw an error. However, all Document.ServiceDTTM columns show NULL. There are no NULL values in the Document table so I am not sure why this is displaying. I am using an incorrect JOIN type somewhere?

    PHP Code:
    DECLARE @StartDate datetime
    DECLARE @EndDate datetime

    SET 
    @StartDate '2015-08-01'
    SET @EndDate '2015-09-01'
    USE Works

    SELECT Patient_Iorg
    .organizationMRN AS MRNIDX_User.AUsername AS TWUserDocument.ServiceDTTMDocumentEvent.eventDTTMDocument_Type_de.EntryName AS NoteName FROM Document (nolock)
        
    JOIN DocumentEvent ON Document.documentid DocumentEvent.documentid
        JOIN Document_Type_de ON Document
    .documenttypede Document_Type_de.ID
        JOIN IDX_User ON DocumentEvent
    .userid IDX_User.ID
        JOIN Person ON Document
    .PatientID Person.ID
        LEFT JOIN Patient_Iorg ON Person
    .id Patient_Iorg.PersonID and Patient_Iorg.InternalOrganization 3
    WHERE 
        DocumentEvent
    .documenteventde IN (18)
    AND
        
    DocumentEvent.eventDTTM >= @StartDate 
    AND 
        
    DocumentEvent.eventDTTM <= @EndDate 
    AND 
        
    Document.Status LIKE 'Final%' 
    AND 
        
    Document.DocumentTypeDE IN (158

    ORDER BY 
        eventDTTM 
    IT OUTPUTS THE FOLLOWING:

    Code:
    XXXXXX	adoctor	    NULL	8/1/2015	Note
    XXXXXX	adoctor	    NULL	8/1/2015	Note
    XXXXXX	aprovider	    NULL	8/1/2015	Note
    XXXXXX	aprovider	    NULL	8/1/2015	Note
    XXXXXX	aprovider	    NULL	8/1/2015	Note
    XXXXXX	aprovider	    NULL	8/1/2015	Note
    XXXXXX	aprovider	    NULL	8/1/2015	Note
    XXXXXX	aprovider	    NULL	8/1/2015	Note
    XXXXXX	aprovider	    NULL	8/1/2015	Note
    Last edited by gmrstudios; April 7th, 2016 at 07:40 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    your joins are fine

    take another look, i'll bet the Document.ServiceDTTM column does have NULLs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo