CODEWITHSUNDEEP
sqlsql-server
Matt
Matt

Reputation: 3970

SQL Query returns nothing if one field is empty

I have the following SQL query:

SELECT 
  C.Child_ID,
  C.Child_Name AS Name,
  C.Child_Surname AS Surname,
  C.Birthday AS DOB, 
  C.Location, 
  C.Primary_Image, 
  U.Text AS MostRecentUpdate,   
  U.Update_Date, 
  S.End_Date
FROM Children as C, Sponsors as S, Updates as U 
WHERE 
  S.User_ID=@UserID 
  AND C.Child_ID=S.Child_ID 
  AND C.Child_ID=U.Child_ID 
  AND U.Update_Date = (SELECT 
                         MAX(Update_Date) 
                       FROM Updates as U2 
                       WHERE U2.Child_ID=S.Child_ID) 
  AND S.End_Date>GETDATE()
  ORDER BY C.Child_Name ASC

Which selects a Child's details, and the child's latest update (it's for a sponsorship website). The problem is that if the child does not have any updates yet (updates are similar to a facebook status), no details are returned.

Is there a way of modifying this query to return just the child details, if no updates are present?

Upvotes: 0

Views: 204

Answers (2)

Steve Stedman
Steve Stedman

Reputation: 2672

You get no results when there are no updates because you are doing an inner join from the child table to the updates table.

I suggest you change it over to the newer ansi compatible join syntax rather than the older join syntax. Then you can just change the join with updates to be a left outer join.

That should do it.

good luck.

Upvotes: 1

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

Use a left outer join:

SELECT C.Child_ID, C.Child_Name AS Name, C.Child_Surname AS Surname,
       C.Birthday AS DOB, C.Location, C.Primary_Image,
       U.Text AS MostRecentUpdate, U.Update_Date, S.End_Date
FROM Children as C
inner join Sponsors S
on C.Child_ID=S.Child_ID
left outer join Updates U 
on C.Child_ID=U.Child_ID
AND  U.Update_Date=(SELECT MAX(Update_Date) FROM Updates as U2 WHERE U2.Child_ID=S.Child_ID)
WHERE S.User_ID=@UserID AND S.End_Date>GETDATE()
ORDER BY C.Child_Name ASC

Upvotes: 4

Related Questions