Monday, February 22, 2010

SQL that return 1 or more rows, but is never empty

The alternate title for this post is "how I worked around the Spring JdbcTemplate queryForObject method throwing an EmptyResultDataAccessException when the query returned zero rows", but that didn't seemed a little verbose.

I added a SELECT 0 as a table source and joined to that to ensure there would always be one row:


SELECT Coalesce(i.UserID, 0) UserID, Coalesce(i.Password, '') Password, Count(a.UserID) Attempts
FROM (SELECT 0 AS id) mstr LEFT JOIN
(SELECT 0 AS id, UserID, UserName, Password FROM User WHERE UserName = 'Scooby') i ON mstr.id = i.id
LEFT JOIN UserAttempts a ON i.UserID = a.UserID AND LastAttempt > 1266602785
GROUP BY i.UserID,i.Password

No comments: