CODEWITHSUNDEEP
sql
MattF
MattF

Reputation: 3

Including Possibly Blank Rows in SQL Query

I would like to create a series of rows from a SQL query where one of the sorting parameters may not exist in the table in certain cases. Here's what I have so far:

SELECT h.DocumentNumber,
    e0.ID as ID, e0.LastName+', '+e0.FirstName AS Employee,
    e0.VpgFunction AS 'VPG Function',
    e0.VpgPosition AS 'VPG Title',
    e0.Grade AS Grade,
    h.Period AS Period,
    e0.Region AS Region,
    e0.Country AS Country,
    e0.Location AS Location,
    e0.Division AS Division,
    e0.Subdivision AS Subdivision,
    e1.LastName+', '+e1.FirstName AS 'First Evaluator',
    e2.LastName+', '+e2.FirstName AS 'Second Evaluator',
    e3.LastName+', '+e3.FirstName AS 'Third Evaluator',
    h1.Data AS 'Overall Performance Evaluation',
    h.State AS State 
    FROM EefHeader h INNER JOIN Employees e0 ON h.Employee = 
    e0.Username INNER JOIN Employees e1 ON h.FirstEvaluator = 
    e1.Username LEFT JOIN Employees e2 ON h.SecondEvaluator =
    e2.Username LEFT JOIN Employees e3 ON h.ThirdEvaluator =
    e3.Username LEFT JOIN EefHistory h1 ON h.DocumentNumber =
    h1.DocumentNumber WHERE h1.DataElementId = 'ManagerOverallEvaluationRbl'
    ORDER BY h.DocumentNumber;

Here's the thing, though. It's possible that for a given DocumentNumber in h1 (EefHistory), there will be no row with a DataElementId that equals 'ManagerrOverallEvaluationRbl' at all. In that case, I'd still like to return that row with 'Overall Performance Evaluation' left blank or NULL or something. Is it possible to do this in a query? (I only ask because the results of the query are later databound to a table. Also, there may or may not be a second and third evaluator, which is why I used LEFT JOIN there.)

Upvotes: 0

Views: 60

Answers (1)

xQbert
xQbert

Reputation: 35323

You have two options.

  1. Included an or on the where clause to handle nulls
  2. follow a more standardized approach and move the limit to the on clause.

Moving it to the on clause forces the engine to execute the limits (apply as a filter) before the join. This in turn allows the left join to function as desired. When you say to include nulls on the or, you don't know if the NULL is the result of the join, or if the value of dataElementID itself is null; which is why it's preferred to apply the limit on the ON clause.

WHERE (h1.DataElementId = 'ManagerOverallEvaluationRbl' OR h1.dataElementID is null)

or 

SELECT h.DocumentNumber,
    e0.ID as ID, e0.LastName+', '+e0.FirstName AS Employee,
    e0.VpgFunction AS 'VPG Function',
    e0.VpgPosition AS 'VPG Title',
    e0.Grade AS Grade,
    h.Period AS Period,
    e0.Region AS Region,
    e0.Country AS Country,
    e0.Location AS Location,
    e0.Division AS Division,
    e0.Subdivision AS Subdivision,
    e1.LastName+', '+e1.FirstName AS 'First Evaluator',
    e2.LastName+', '+e2.FirstName AS 'Second Evaluator',
    e3.LastName+', '+e3.FirstName AS 'Third Evaluator',
    h1.Data AS 'Overall Performance Evaluation',
    h.State AS State 
    FROM EefHeader h INNER JOIN Employees e0 ON h.Employee = 
    e0.Username INNER JOIN Employees e1 ON h.FirstEvaluator = 
    e1.Username LEFT JOIN Employees e2 ON h.SecondEvaluator =
    e2.Username LEFT JOIN Employees e3 ON h.ThirdEvaluator =
    e3.Username LEFT JOIN EefHistory h1 ON h.DocumentNumber = h1.DocumentNumber and 
    h1.DataElementId = 'ManagerOverallEvaluationRbl'
    ORDER BY h.DocumentNumber;

To handle the additional requirement in the comment simply generate a data set which consists of the max revision number for each document and join to that set. Depending on desires results we may have to alter the existing left join to do this join between h1 and h2 as a whole subselect. Not sure if the dataElementID applies to both joins or just the one based on

This is one approach: simply adding this as another left join.... 

LEFT JOIN (Select max(h1.revsionNumber), documentNumber 
   from EefHistory group by documentNumber) h2
 on h2.documentNumber = h1.documentNumber 
and h2.RevisionNumber = h1.RevisionNumber

or possibly this.

SELECT h.DocumentNumber,
    e0.ID as ID, e0.LastName+', '+e0.FirstName AS Employee,
    e0.VpgFunction AS 'VPG Function',
    e0.VpgPosition AS 'VPG Title',
    e0.Grade AS Grade,
    h.Period AS Period,
    e0.Region AS Region,
    e0.Country AS Country,
    e0.Location AS Location,
    e0.Division AS Division,
    e0.Subdivision AS Subdivision,
    e1.LastName+', '+e1.FirstName AS 'First Evaluator',
    e2.LastName+', '+e2.FirstName AS 'Second Evaluator',
    e3.LastName+', '+e3.FirstName AS 'Third Evaluator',
    h1.Data AS 'Overall Performance Evaluation',
    h.State AS State 
    FROM EefHeader h 
    INNER JOIN Employees e0 
      ON h.Employee = e0.Username 
    INNER JOIN Employees e1 
      ON h.FirstEvaluator = e1.Username 
    LEFT JOIN Employees e2 
      ON h.SecondEvaluator = e2.Username 
    LEFT JOIN Employees e3 
      ON h.ThirdEvaluator = e3.Username 
    LEFT JOIN (Select * from EefHistory h1
               INNER JOIN (SELECT max(revsionNumber) mrn, documentNumber 
                           FROM EefHistory GROUP BY documentNumber) h2
                   on h2.documentNumber = h1.documentNumber 
                  and h2.mrn = h1.RevisionNumber
                  and h1.DataElementId = 'ManagerOverallEvaluationRbl'
       ON h.DocumentNumber = z.DocumentNumber
    ORDER BY h.DocumentNumber;

The intent here is to get a set of eefHistory which contains all columns but only the max document and revision number where the dataElementID is 'ManagerOverallEvaluationRbl'

Upvotes: 2

Related Questions