title: SQL query return null results yet there are values in the database
body: <p>I am creating a certain report that requires joining two tables and creating a sub queries. Below is the code</p>
  
  <pre><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
  </code></pre>
  
  <p>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</p>
  
  <p><a href="https://i.stack.imgur.com/y7ueq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/y7ueq.png" alt="query results"></a>
  Changed the structure of the query as below</p>
  
  <pre><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
      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
  </code></pre>
  
  <p>The results are like this:</p>
  
  <p><a href="https://i.stack.imgur.com/C4h02.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/C4h02.png" alt="query results"></a></p>