Display records with count 0 - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Display records with count 0

Author

Topic

kprasadreddy
Starting Member

41 Posts

Posted - 2005-03-23 : 13:34:00

I am doing a count and everything works fine but If there are not students for a course it doesn't display the count.How can display rows where we have a CRS_NB even we don't have a count

SELECT count(DISTINCT LD180.STDNT_NB) as STDNT_COUNT,
LD075.CRS_NB,
LD030.CRS_NM
FROM ZLDT010_STDNT LD010
INNER JOIN ZLDT180_SCR LD180
ON LD010.STDNT_NB = LD180.STDNT_NB
inner JOIN ZLDT075_CRRCLM_CRS LD075
ON LD180.CRS_NB = LD075.CRS_NB
AND LD180.CRS_VRSN_NB = LD075.CRS_VRSN_NB
INNER JOIN ZLDT030_CRS LD030
ON LD030.CRS_NB = LD180.CRS_NB
AND LD030.CRS_VRSN_NB = LD180.CRS_VRSN_NB
WHERE LD075.CRRCLM_CD = 4
AND LD075.CRRCLM_TRACK_CD = 75
AND LD075.CRS_REQIR_CD IN (1,2)
AND (LD075.LOC_CD = 'BA' OR LD075.LOC_CD ='ZZ')
AND LD010.SUPV_USER_ID_CD ='bcox2'
and LD010.STAT_IN = 1
aND LD010.CRRCLM_CD = 4
AND LD010.CRRCLM_TRACK_CD = 75
AND LD010.LOC_CD = 'BA'
group by LD030.CRS_NM,
LD075.CRS_NB
order by LD030.CRS_NM

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 13:42:03

Are one of these tasbles a course table?

Make that the driver and left join the other tables

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 13:42:59

You haven't posted your table structure, sample data, or the expected result set, so I'm not sure how we can help you.

Tara

kprasadreddy
Starting Member

41 Posts

Posted - 2005-03-23 : 13:50:23

Sample Data...

I need to get the records for courses even if the count is zero and the table driving the CRS_NB is 75 table and count is 180 table and both tables are linked on CRS_NB and CRS_VRSN_NB
Count crs_nb Crs_nm
1 9096 CCEP (Collection Call Efficiency Program)
3 9144 CentreVu Supervisor
3 9424 Coaching for Quality Relationships
3 9396 Encouraging the Heart
3 9287 Leading Change: Who Moved My Cheese?
1 9171 Metrics Based Coaching
3 9291 NICE
1 9286 Personality Styles
1 9324 SC Interview Process
3 9311 Service Center Technology Overview
3 9133 WorkFlow for Supervisors
3 9100 WorkFlow: Management Reports

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 13:54:47

So where's the table structure for all of your tables and sample data for all of your tables? You've only provided the expected result set. Also, the expected result set should reflect the sample data of your tables. Otherwise, we aren't able to test it.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-23 : 14:07:04

quote:
Originally posted by tduggan

So where's the table structure for all of your tables and sample data for all of your tables? You've only provided the expected result set. Also, the expected result set should reflect the sample data of your tables. Otherwise, we aren't able to test it.

Tara

Gee, Tara--I mean, SQL Goddess---you sure are rough on all the newbies today!

What ever happened to the Warrior Queen?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 14:11:45

Xerxes, how do you expect us to help without more information?

Tara

kprasadreddy
Starting Member

41 Posts

Posted - 2005-03-23 : 14:17:36

I gave the query and the expected result.How can I send you the database diagram?

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-23 : 14:18:10

SQL Goddess---

I was being facetious and sarcastic

Personally, I'm always AMAZED

that you are able to answer half the questions you do without all the other information you'd normally need!

Frankly, I have found your efforts to be nothing short of EXCEPTIONAL and PRAISEWORTHY And I mean that most sincerely!

You have a great day!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 14:26:35

Go in to Enterprise Managers, Right Click on a table, you'll get a cursor menu, select ALL TASKS> Script Datbaase

One more time.

Which one of these tables is a list of all the courses....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-23 : 14:27:40

You are going to need a LEFT OUTER JOIN somewhere in that query as Brett mentioned. Your table names don't help us understand your problem. Perhaps you could rewrite it so that it is more understandable for us. Like for your students table, name is students and alias it s. For your course table, name it course and alias it c.

You probably need to make this more simple also so that you can figure out where the query is going wrong. Get rid of all of the logic that you have and start with two tables. Make sure the result set is correct. If rows are missing, you'll need LEFT OUTER JOIN instead of INNER JOIN. Do you understand the difference between those?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 14:38:22

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myCourses99(CourseId int, CourseName varchar(50))
CREATE TABLE myStudents99(StudentId int, StudentName varchar(50))
CREATE TABLE myStudentsCourses99(StudentId int, CourseId int)
GO

INSERT INTO myCourses99(CourseID, CourseName)
SELECT 1, 'Mixology' UNION ALL
SELECT 2, 'How to Use Profiler' UNION ALL
SELECT 3, 'How to Snowboard with out Breaking your neck' UNION ALL
SELECT 4, 'Bonde Beach points of interest' UNION ALL
SELECT 5, 'mySQL Unleashed'

INSERT INTO myStudents99(StudentID, StudentName)
SELECT 1, 'Brett' UNION ALL
SELECT 2, 'Tara' UNION ALL
SELECT 3, 'Bill' UNION ALL
SELECT 4, 'Rob' UNION ALL
SELECT 5, 'Damain' UNION ALL
SELECT 6, 'Jeff' UNION ALL
SELECT 7, 'Nigel'

INSERT INTO myStudentsCourses99(StudentId, CourseId)
SELECT 1,1 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,4 UNION ALL
SELECT 5,1 UNION ALL
SELECT 6,4
GO

SELECT c.CourseName, COUNT(sc.StudentId)
FROM myCourses99 c
LEFT JOIN myStudentsCourses99 sc
ON c.CourseID = sc.CourseID
GROUP BY c.CourseName
GO

SET NOCOUNT OFF
DROP TABLE myCourses99
DROP TABLE myStudents99
DROP TABLE myStudentsCourses99
GO

[/code]

Brett

8-)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-03-23 : 15:13:01

Brett,

<SARCASM>
Where are the keys on those tables? Where is the functional dependency spec? What normal form is the schema in? What about the RI constraints? The DROP TABLE can shortened to be DROP TABLE myCourses99, myStudents99, myStudentsCourses99. You are missing the owner directives during tble operations.
</SARCASM>

Seriously mate, you are way to kind/thorough in your replies. Shouldn't you be teaching DB2 to play nice with SQL Server?

DavidM

A front-end is something that tries to violate a back-end.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-23 : 15:20:31

quote:
Originally posted by byrmol

The DROP TABLE can shortened to be DROP TABLE myCourses99, myStudents99, myStudentsCourses99.

You know, I saw that just the other day...I should start using that...

DB2 OS/390 doesn't play nice with anyone...ever see what a DB2 External Stored procedure looks like?

SQL Server is so hot for CRL integration...hell I was hoping DB2 would have some Internal procedural language like SQL Server...Rexx maybe....it's like they're going the wrong way...

This persons data structure btw looks like some 3rd party vendor package...looks like PeopleSoft...

Brett

8-)

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-24 : 00:11:08

Hi kprasadreddy,

Tara asking table structure not your data.If you give your table structure,you may get accurate solution from forum.

:) While we stop to think, we often miss our opportunity :)

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-24 : 09:27:10

quote:
Originally posted by SqlStar

Hi kprasadreddy,

Tara asking table structure not your data.If you give your table structure,you may get accurate solution from forum.

:) While we stop to think, we often miss our opportunity :)

Whaddya mean...may

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources