Need a line output if record does not exist (or is null)

Transact-SQL
1

Running into a SQL query conundrum...
I need to return the STATE name and a '0' if the record is not found.

IN the below SQL Query MAINE, at the moment, has zero miles therefore the state name has not yet made it into the database yet. They are added as miles have been driven in the state.

The base query works fine so long as I have miles recorded for a particular state.

I still need an output from the DB somehow to basically show as Tennessee does below::

STATE Miles
TENNESSEE 13196

My intended outcome would be to still give the state NAME and then a '0' since no miles have been recorded, as shown below.:

STATE Miles
MAINE 0

if exists (select STATE FROM State_Miles WHERE State='Maine')

BEGIN
select STATE, sum(MILES) as Miles
FROM State_Miles
WHERE State='Maine'
Group by STATE
END

2

sum(isnull(MILES,0))

3

That does not give me the outcome desired. Maybe it is impossible if the state Maine does not exist in the database?

4

This is where you hit the wall of tables not normalized.
You need a table of states with stateid as prinmary key.
Your other tables will also need stateid as foreign key to this table
Then you

Select * 
from tblStates s 
Left join tblStateMileage

And you will get Maine even if it does not have any mileage associated with it

5

Yeah, this is what I assumed would end up being the case but was trying to shortcut it a bit. I got around it by adding Maine with 0 miles into the table and that resolved this issue.I'll just have to remember to do this each year or actually put some miles in Maine! LOL... or do as you suggest and create a state table. Will work on this tomorrow and then go from there. Thanks yosiasz and bitsmed of you for the assist. I'm learning a lot fast about SQL!!

6 
create table states(stateid int not null identity(1,1), statename varchar(150), statenameabbr varchar(10) )

insert into states(statename, statenameabbr)
select 'Alabama' statename, 'AL' as stateabbr union 
select 'Alaska' statename, 'AK' as stateabbr union 
select 'Arizona' statename, 'AZ' as stateabbr union 
select 'Arkansas' statename, 'AR' as stateabbr union 
select 'California' statename, 'CA' as stateabbr union 
select 'Colorado' statename, 'CO' as stateabbr union 
select 'Connecticut' statename, 'CT' as stateabbr union 
select 'Delaware' statename, 'DE' as stateabbr union 
select 'District of Columbia' statename, 'DC' as stateabbr union 
select 'Florida' statename, 'FL' as stateabbr union 
select 'Georgia' statename, 'GA' as stateabbr union 
select 'Hawaii' statename, 'HI' as stateabbr union 
select 'Idaho' statename, 'ID' as stateabbr union 
select 'Illinois' statename, 'IL' as stateabbr union 
select 'Indiana' statename, 'IN' as stateabbr union 
select 'Iowa' statename, 'IA' as stateabbr union 
select 'Kansas' statename, 'KS' as stateabbr union 
select 'Kentucky' statename, 'KY' as stateabbr union 
select 'Louisiana' statename, 'LA' as stateabbr union 
select 'Maine' statename, 'ME' as stateabbr union 
select 'Montana' statename, 'MT' as stateabbr union 
select 'Nebraska' statename, 'NE' as stateabbr union 
select 'Nevada' statename, 'NV' as stateabbr union 
select 'New Hampshire' statename, 'NH' as stateabbr union 
select 'New Jersey' statename, 'NJ' as stateabbr union 
select 'New Mexico' statename, 'NM' as stateabbr union 
select 'New York' statename, 'NY' as stateabbr union 
select 'North Carolina' statename, 'NC' as stateabbr union 
select 'North Dakota' statename, 'ND' as stateabbr union 
select 'Ohio' statename, 'OH' as stateabbr union 
select 'Oklahoma' statename, 'OK' as stateabbr union 
select 'Oregon' statename, 'OR' as stateabbr union 
select 'Maryland' statename, 'MD' as stateabbr union 
select 'Massachusetts' statename, 'MA' as stateabbr union 
select 'Michigan' statename, 'MI' as stateabbr union 
select 'Minnesota' statename, 'MN' as stateabbr union 
select 'Mississippi' statename, 'MS' as stateabbr union 
select 'Missouri' statename, 'MO' as stateabbr union 
select 'Pennsylvania' statename, 'PA' as stateabbr union 
select 'Rhode Island' statename, 'RI' as stateabbr union 
select 'South Carolina' statename, 'SC' as stateabbr union 
select 'South Dakota' statename, 'SD' as stateabbr union 
select 'Tennessee' statename, 'TN' as stateabbr union 
select 'Texas' statename, 'TX' as stateabbr union 
select 'Utah' statename, 'UT' as stateabbr union 
select 'Vermont' statename, 'VT' as stateabbr union 
select 'Virginia' statename, 'VA' as stateabbr union 
select 'Washington' statename, 'WA' as stateabbr union 
select 'West Virginia' statename, 'WV' as stateabbr union 
select 'Wisconsin' statename, 'WI' as stateabbr union 
select 'Wyoming' statename, 'WY' as stateabbr 


create table statemiles(stateid int, milemonth int, mileyear int, miles int)

;with src
as
(
	select 'Alabama' as statename, 1 as milemonth, 2018 mileyear, 589 miles
)
insert into statemiles
select stateid, milemonth, mileyear, miles
 from src 
 join states s on src.statename = s.statename