CODEWITHSUNDEEP
sqlsql-server
DENNIS KITHINJI
DENNIS KITHINJI

Reputation: 236

SQL query return null results yet there are values in the database

I am creating a certain report that requires joining two tables and creating a sub queries. Below is the code

WITH temp_ticket_rows (client_id, last_name,first_name, pet_name) AS 
(   
    SELECT 
        C.fldClientID, C.fldLastName, C.fldFirstName, P.fldName     
    FROM 
        [OneTwoThreePet].[dbo].[tblPets] P   
    RIGHT OUTER JOIN 
        tblClients C ON P.fldClientID = C.fldClientID
),  temp_tips ([Last Name], [First Name], [Pet Name]) AS 
( 
    SELECT  
        C.fldLastName, C.fldFirstName, 
        (STUFF((SELECT DISTINCT ', ' + P.fldName[text()]
                FROM [OneTwoThreePet].[dbo].[tblPets] P
                WHERE P.fldClientID = C.fldClientID 
                  AND p.fldInactive IN ('0','')
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2,' ')) AS [Pet Name]   
    FROM 
        temp_ticket_rows P
    LEFT OUTER JOIN
        tblClients C ON C.fldClientID = P.client_id
) 
SELECT
    UPPER(LEFT([Last Name], 1)) + LOWER(SUBSTRING([Last Name], 2, LEN([Last Name]))) AS [LAST NAME],
    UPPER(LEFT([First Name], 1)) + LOWER(SUBSTRING([First Name], 2, LEN([First Name]))) AS [FIRST NAME],
    [Pet Name] AS [PET NAME],
    COUNT (*) AS [PET NUMBER] 
FROM
    temp_tips 
--WHERE [Pet Name] IS NOT NULL
GROUP BY [Last Name], [First Name], [Pet Name] 
ORDER BY [Last Name] ASC

The results from above query gives some null which are not supposed to be there since there is value. Below is the results of the query you can see the nulls yet pet number counts to 1

query results Changed the structure of the query as below

WITH temp_ticket_rows (client_id, last_name,first_name, pet_name) AS 
( 
    SELECT 
        C.fldClientID, C.fldLastName, C.fldFirstName, P.fldName
    FROM
        [OneTwoThreePet].[dbo].[tblPets] P
    INNER JOIN 
        tblClients C ON P.fldClientID = C.fldClientID
), temp_tips ([Last Name], [First Name], [Pet Name]) AS 
( 
    SELECT
        C.fldLastName, C.fldFirstName,
        (STUFF((SELECT DISTINCT ', ' + P.fldName[text()]
                FROM [OneTwoThreePet].[dbo].[tblPets] P
                WHERE P.fldClientID = C.fldClientID 
                  AND p.fldInactive IN ('0', '')
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')) as [Pet Name] 
    FROM 
        temp_ticket_rows P
    LEFT OUTER JOIN
        tblClients C ON C.fldClientID = P.client_id
)
SELECT
    UPPER(left( [Last Name],1)) + LOWER(SUBSTRING([Last Name],2,LEN([Last Name]))) AS [LAST NAME],
    UPPER (left ([First Name],1)) + LOWER(SUBSTRING([First Name],2,LEN([First Name]))) AS [FIRST NAME],
    [Pet Name] AS [PET NAME],
    COUNT ([Pet Name]) AS [PET NUMBER]
FROM
    temp_tips 
--WHERE [Pet Name] IS NOT NULL
GROUP BY 
    [Last Name], [First Name], [Pet Name]
ORDER BY 
    [Last Name] ASC

The results are like this:

query results

Upvotes: 1

Views: 2886

Answers (1)

Mittal Patel
Mittal Patel

Reputation: 2762

You have used COUNT (*) AS [PET NUMBER] so you get the result as 1 even if the PET NAME is NULL

Change the COUNT (*) AS [PET NUMBER] to COUNT ([PET NUMBER]) AS [PET NUMBER] and check. You must get the result as 0 where the [PET NUMBER] is null.

Upvotes: 2

Related Questions