CODEWITHSUNDEEP
sqlsql-server
Kivan Ilangakoon
Kivan Ilangakoon

Reputation: 457

how to omit null values using SQL query

I am trying to only display the rows in which there is date for Researchers. I cannot manage to omit the rows with Null Values. I even tried this solution How to remove null rows from sql query result?..

This is my Query:

SELECT Submission.Title AS [Submission_Title], CA.Surname AS [Researchers], Submission.Status AS [Status] 
FROM Submission 
CROSS APPLY (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
FROM ResearcherSubmission rs INNER JOIN Researcher r 
ON r.ResearcherID = rs.ResearcherID 
WHERE CONCAT (DATENAME(MONTH,[Submission].[SubmissionDate]), ' ',DATEPART (YEAR,[Submission].[SubmissionDate])) = 'October 2015'  
AND Submission.SubmissionID = rs.SubmissionID 
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')) AS CA (Surname) 
GROUP BY convert(varchar(10),datename(month,Submission.SubmissionDate)), Submission.Title, CA.Surname, Submission.Status;

This is my Current output:

enter image description here

any suggestion. Thank you

Upvotes: 2

Views: 4583

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You should filter out the researchers before the group by rather than afterwards. When possible, it is better (performance-wise) to put conditions before aggregation.

SELECT s.Title AS Submission_Title, CA.Surname AS Researchers, s.Status 
FROM Submission s CROSS APPLY
     (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
                    FROM ResearcherSubmission rs INNER JOIN
                         Researcher r 
                         ON r.ResearcherID = rs.ResearcherID 
                    WHERE s.SubmissionID = rs.SubmissionID 
                    FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'
                   ), 1, 2, ' '))
    ) AS CA(Surname) 
WHERE s.SubmissionDate >= '2015-10-01' AND s.SubmissionDate < '2015-11-01' AND
      ca.Surname IS NULL
GROUP BY YEAR(s.SubmissionDate), MONTH(s.SubmissionDate), s.Title, CA.Surname, s.Status;

Note the changes made:

  • Table aliases make the query easier to write and to read.
  • I changed the date comparison to have no functions on the date itself. This would allow SQL Server to use an index, if appropriate.
  • I also moved the date comparison from the CROSS APPLY subquery to the outer query. This could be a big gain in efficiency. Why do the extra work for rows that will be filtered out anyway?
  • I added the NOT NULL condition to the WHERE clause.
  • The date key in the outer GROUP BY is redundant because the query is only using one month of data. I simplified the logic but left it.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

Quickfix, without reading query:

WITH cte AS
(
SELECT Submission.Title AS [Submission_Title], CA.Surname AS [Researchers], Submission.Status AS [Status] 
FROM Submission 
CROSS APPLY (SELECT STUFF((SELECT DISTINCT ', ' + r.Surname 
FROM ResearcherSubmission rs INNER JOIN Researcher r 
ON r.ResearcherID = rs.ResearcherID 
WHERE CONCAT (DATENAME(MONTH,[Submission].[SubmissionDate]), ' ',DATEPART (YEAR,[Submission].[SubmissionDate])) = 'October 2015'  
AND Submission.SubmissionID = rs.SubmissionID 
FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')) AS CA (Surname) 
GROUP BY convert(varchar(10),datename(month,Submission.SubmissionDate)), Submission.Title, CA.Surname, Submission.Status
)
SELECT *
FROM cte
WHERE Researchers IS NOT NULL;

There is probably more elegant solution, but you need to share sample data and structures.

This part may cause problems:

SELECT DISTINCT ', ' + r.Surname

try with CONCAT instead or :

SELECT DISTINCT ', ' + ISNULL(r.Surname, '')

Upvotes: 2

Related Questions