To retrive a row even it has no data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

To retrive a row even it has no data

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-06-04 : 10:09:22

Hi,

i have written one simple query to retrive items information from each location.

SELECT DISTINCT
CASE Location
WHEN 'Tx' THEN 'TEXAS'
WHEN 'NJ' THEN 'New Jersy'
WHEN 'NC' THEN 'North Carolina'
ELSE 'OTHER' END AS Location,
Items
FROM
ItemsByLocation where date= @date

From above query i am getting data for each location.
but For example if Location NJ has no rows for that particular date
then i am getting like

Location Items

TEXAS 120
North Carolina 75

Its not retruning any row for Location New Jercy bcoz it has no items but according to my requirement it should return with items as Zero values

Location Items

TEXAS 120

North Carolina 75

New Jercy 0

Can you please help me on this..

Archana

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 10:23:32

you need to have a master table for locations like


Location                LocCode
TEXAS                   Tx
NewJersey               NJ
North Carolina          NC
....

and then use like 

SELECT lm.Location, 
COALESCE(i.Items ,0) AS Items
FROM LocationMaster lm
LEFT JOIN ItemsByLocation i
ON i.Location = lm.LocCode
AND i.date= @date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

archana23
Yak Posting Veteran

89 Posts

Posted - 2012-06-04 : 10:30:05

Thanks for reply.

I dont want to get zero's for other locations. I need to show zero for only NJ location.

Thanks..

Archana

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources