Help wth SQL; select of no records

I have a query:

SELECT ID FROM admin_zipcode WHERE zipcode = 11111,   where 11111 does not exist in my table.

Instead of returning no records, I would like to return a record where ID =0 for any case where the zipcode does not exist.   How can I do this?
LVL 1
HLRosenbergerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT TOP (1) *
FROM (
    SELECT ID
    FROM admin_zipcode
    WHERE zipcode = 11111
    UNION ALL
    SELECT 0
) AS derived
ORDER BY ID DESC
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I would like to return a record where ID =0 for any case where the zipcode does not exist.
In that case you need a table of all zipcodes, so it knows that 0 is a valid selection but not in the data and still should be returned, as opposed to not a valid selection.  Something like...
SELECT az.zipcode, COUNT(z.zipcode)
FROM all_zip_codes_table az
  -- The LEFT means return all values from az in the return set
  LEFT JOIN admin_zipcode z ON az.zipcode = z.zipcode   
GROUP BY az.zipcode
ORDER BY az.zipcode

Open in new window

0
 
HLRosenbergerAuthor Commented:
Terrific!  thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.