SQL won't display all data needed

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

SQL won't display all data needed

Author

Topic

SimonD
Starting Member

1 Post

Posted - 2010-11-04 : 11:03:02

Hello,

I'm fairly new to SQL programming and can't work out how to return the data I want.

I have the following 2 tables:

CategoryList
------------
CategoryId int
Category varchar(50)

CategorySubmissions
-------------------
RowId int
CategoryId int
DateSubmitted datetime

Currently the tables contain the following data:

CategoryList:

CategoryId Category
---------- --------
1 Registration
2 Documents
3 Reporting
4 Accounts

CategorySubmissions:

RowId CategoryId DateSubmitted
----- ---------- -------------
1 1 02/11/2010 16:40:00
2 1 03/11/2010 10:57:10
3 2 03/11/2010 10:57:19
4 3 03/11/2010 11:02:10

I'm trying to write sql so it displays *all* categories regardless of whether there have been any submissions i.e.

Category TotalSubmissions
-------- ----------------
Accounts 0
Documents 1
Registration 2
Reporting 1

The SQL I've written so far is:

select cl.Category, count(cs.CategoryId) as 'TotalSubmissions'
from CategorySubmissions cs
left outer join CategoryList cl on cs.CategoryId = cl.CategoryId
where (cs.DateSubmitted between convert(datetime, '02/11/2010 00:00:00', 103)
and convert(datetime, '04/11/2010 23:59:59', 103))
group by cl.Category
order by cl.Category

The problem with this is it won't display the row with 0 submissions. Because my SQL experience is limited, I'm not sure if the SQL is wrong or the table design or something else.

I would be grateful for any advice.

Thanks

Simon

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-04 : 12:11:08

Dependoing on how you look at you either have the join order backwards or you need to use a RIGHT OUTER JOIN. In a LEFT join the table to the LEFT is the one then you get all the values for, not the right one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-10 : 10:30:24

seeing your explanation i think your base table should be categorylist from where you need to link to other table using left join to get submission info

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

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-11-10 : 16:20:26

Try this ...

select cl.Category, count(cs.CategoryId) as 'TotalSubmissions'
from CategorySubmissions cs
right outer join CategoryList cl on cs.CategoryId = cl.CategoryId
and (cs.DateSubmitted between '02/11/2010 00:00:00' and '04/11/2010 23:59:59')
group by cl.Category
order by cl.Category

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-11 : 05:09:16

quote:
Originally posted by namman

Try this ...

select cl.Category, count(cs.CategoryId) as 'TotalSubmissions'
from CategorySubmissions cs
right outer join CategoryList cl on cs.CategoryId = cl.CategoryId
and (cs.DateSubmitted between '02/11/2010 00:00:00' and '04/11/2010 23:59:59')
group by cl.Category
order by cl.Category

Use

(cs.DateSubmitted >='20101102' and cs.DateSubmitted <'20101105')

in place of

(cs.DateSubmitted between '02/11/2010 00:00:00' and '04/11/2010 23:59:59')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources