SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 [Resolved] Getting a Count from different table?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DTFan
Yak Posting Veteran

USA
52 Posts

Posted - 12/09/2005 :  14:05:20  Show Profile  Reply with Quote
I *hope* that I explain and do this correctly. OK. I'm trying to figure out how to get a count from a table not needed elsewhere in the query. I have a couple of tables that I'm using in my query (tblStudent, tblMajor, tblStudentType) which is returning information that I requested. I want to add one more item to the Select statement using a fourth table (tblStudentClass). The query now is returning a list of all students, their "Type" (Full Time, Part Time, etc), their name, email address and major. What I would like to add is a Count from the fourth table as to how many time that studentID is listed (which would indicate how many classes they are currently enrolled in). I'm including a script which was generated by the GUI that I'm using for MSDE 2000. I've removed the comments.


CREATE TABLE [tblStudent] (
  [studID] int IDENTITY(1, 1) NOT NULL,
  [majID] tinyint NOT NULL,
  [stypeID] tinyint NOT NULL,
  [studFName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
  [studLName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
  [studEmail] varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'major ID foreign key', 'user', 'dbo', 'table', 'tblStudent', 'column', 'majID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'student type', 'user', 'dbo', 'table', 'tblStudent', 'column', 'stypeID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students first name', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studFName'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students last name', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studLName'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students email address', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studEmail'
GO

CREATE TABLE [tblMajor] (
  [majID] tinyint IDENTITY(1, 1) NOT NULL,
  [depID] tinyint NOT NULL,
  [majTitle] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'what department is this major a part of?', 'user', 'dbo', 'table', 'tblMajor', 'column', 'depID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'name of the major?', 'user', 'dbo', 'table', 'tblMajor', 'column', 'majTitle'
GO

CREATE TABLE [tblStudentClass] (
  [scID] int IDENTITY(1, 1) NOT NULL,
  [studID] int NOT NULL,
  [claID] int NOT NULL,
  [profID] int NOT NULL,
  [schedID] int NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'students ID (foreign key)', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'studID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'tblclass foreign key', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'claID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'f/k to professor table', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'profID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'what schedule is this class on (f/k to tblSchedule)', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'schedID'
GO

CREATE TABLE [tblStudentType] (
  [stypeID] tinyint IDENTITY(1, 1) NOT NULL,
  [stypeTitle] varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

SET IDENTITY_INSERT [tblMajor] ON
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (1, 1, 'Temp Major')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (2, 3, 'Physics')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (3, 2, 'American Lit')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (4, 10, 'European Civilization')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (5, 9, 'Political Science')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (6, 2, '18th Century Poetry')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (7, 4, 'Sports Physiology')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (8, 8, 'Drama')
GO

INSERT INTO [tblMajor] ([majID], [depID], [majTitle])
VALUES 
  (9, 10, 'American History')
GO

SET IDENTITY_INSERT [tblMajor] OFF
GO

COMMIT
GO

SET IDENTITY_INSERT [tblStudent] ON
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (1, 1, 1, 'Temp', 'Student', 'no@no.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (2, 9, 1, 'Ali', 'Larter', 'ali@finaldestination.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (3, 9, 1, 'Amy', 'Smart', 'amy@butterflyeffect.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (4, 8, 2, 'Katie', 'Holmes', 'katie@thecreek.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (5, 9, 1, 'Jordana', 'Brewster', 'jordana@fastandfurious.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (6, 7, 3, 'Anna', 'Kournikova', 'anna@tennis.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (7, 2, 2, 'Rhona', 'Mitra', 'rhona@niptuck.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (8, 5, 1, 'Julie', 'Bowen', 'jbowen@bostonlegal.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (9, 2, 4, 'Lake', 'Bell', 'lakeb@surface.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (10, 4, 3, 'Keira', 'Knightly', 'keira@thejacket.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (11, 7, 2, 'Jennifer', 'Morrison', 'jen@house.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (12, 8, 3, 'Evangeline', 'Lilly', 'elilly@lost.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (13, 9, 4, 'Jennifer', 'Finnigan', 'jennifer@closetohome.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])
VALUES 
  (14, 6, 2, 'Mila', 'Kunis', 'mila@that70sshow.com')
GO

SET IDENTITY_INSERT [tblStudent] OFF
GO

COMMIT
GO

SET IDENTITY_INSERT [tblStudentClass] ON
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (1, 1, 1, 1, 1)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (2, 2, 22, 11, 4)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (3, 2, 4, 34, 14)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (4, 10, 9, 17, 5)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (5, 5, 4, 34, 14)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (6, 5, 20, 11, 12)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (7, 5, 21, 23, 35)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (8, 2, 21, 23, 35)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (9, 8, 18, 21, 5)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (10, 8, 19, 72, 27)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (11, 8, 9, 17, 5)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (12, 12, 23, 33, 10)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (13, 12, 8, 91, 19)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (14, 9, 2, 64, 9)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (15, 9, 14, 6, 15)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (16, 11, 7, 51, 9)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (17, 2, 20, 11, 12)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (18, 14, 17, 81, 10)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (19, 14, 15, 40, 4)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (20, 3, 20, 11, 12)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (21, 13, 20, 11, 12)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (22, 13, 4, 34, 14)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (23, 4, 19, 72, 27)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (24, 3, 14, 6, 15)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (25, 3, 11, 17, 8)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (26, 3, 22, 11, 4)
GO

INSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])
VALUES 
  (27, 3, 18, 21, 5)
GO

SET IDENTITY_INSERT [tblStudentClass] OFF
GO

COMMIT
GO

SET IDENTITY_INSERT [tblStudentType] ON
GO

INSERT INTO [tblStudentType] ([stypeID], [stypeTitle])
VALUES 
  (1, 'Full Time')
GO

INSERT INTO [tblStudentType] ([stypeID], [stypeTitle])
VALUES 
  (2, 'Part Time')
GO

INSERT INTO [tblStudentType] ([stypeID], [stypeTitle])
VALUES 
  (3, 'Graduate')
GO

INSERT INTO [tblStudentType] ([stypeID], [stypeTitle])
VALUES 
  (4, 'Evening')
GO

SET IDENTITY_INSERT [tblStudentType] OFF
GO

COMMIT
GO

ALTER TABLE [dbo].[tblStudent]
ADD CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED ([studID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblMajor]
ADD CONSTRAINT [PK_tblMajor] PRIMARY KEY CLUSTERED ([majID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblStudentClass]
ADD CONSTRAINT [PK_tblStudentClass] PRIMARY KEY CLUSTERED ([scID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblStudentType]
ADD PRIMARY KEY CLUSTERED ([stypeID])
ON [PRIMARY]
GO


The query that I have so far is this:


SELECT 
  stype.stypeTitle,
  stud.studFName + ' ' + stud.studLName AS studName,
  stud.studEmail,
  maj.majTitle
FROM
  tblStudent AS stud
  INNER JOIN tblMajor AS maj ON (stud.majID = maj.majID)
  INNER JOIN tblStudentType AS stype ON (stud.stypeID = stype.stypeID)
WHERE stud.studID > 1
ORDER BY  stype.stypeID ASC, stud.studLName ASC, stud.studFName ASC


That works, but it's not returning the Count (which I want to be the last column). So the output should look like this (without, of course, the pipes):

quote:

Full Time | Julie Bowen | emailaddresshere | Political Science | 3
Full Time | Jordana Brewster | emailaddresshere | American History | 3
Full Time | Ali Larter | emailaddresshere | American History | 4
Full Time | Amy Smart | emailaddresshere | American History | 5
Part Time | Katie Holmes | emailaddresshere | Drama | 1
Part Time | Mila Kunis | emailaddresshere | 18th Century Poetry | 2
Part Time | Rhona Mitra | emailaddresshere | Physics | 0
Part Time | Jennifer Morrison | emailaddresshere | Sports Physiology | 1
Graduate | Keira Knightly | emailaddresshere | European Civilization | 1
Graduate | Anna Kournikova | emailaddresshere | Sports Physiology | 0
Graduate | Evangeline Lilly | emailaddresshere | Drama | 2
Evening | Lake Bell | emailaddresshere | Physics | 2
Evening | Jennifer Finnigan | emailaddresshere | American History | 2


Anyway, I've tried including a Count(tblStudentClass.studID), but all it returns are 1's for each person (although each listing in tblSTudentClass was returned so you could see Amy Smart listed 5 times, Ali Larter listed 4 times, etc.). As you can see from the query, I'm getting the students grouped by their type and then sorting by last name, first name. As I said, this query is working exactly as I'd like except that I can't get a count to show up.

Any idea's?

Thank you in advance for any and all help/assistance/tutorials/pointers etc that you can give. I'm pretty sure the script should work as it did work on my computer (once I had the database created (with no tables)). I'm using EMS MSSQL Manager as the GUI in case that matters. If there is anything else I need to provide, please let me know.

And thanks again for any help that you can give.

DTFan
Ever-hopeful programmer-in-training

Edited by - DTFan on 12/12/2005 08:17:33

nu_dba
Starting Member

8 Posts

Posted - 12/09/2005 :  15:17:58  Show Profile  Reply with Quote
When you tried:

Anyway, I've tried including a Count(tblStudentClass.studID), but all it returns are 1's for each person

did you also INNER JOIN the tblStudentClass table with the tblStudent ON (stud.studID = tblStudentClass.studID)? I don't have access to a DB right now to play around with your tables so I'm trying to visual how the join with the student class table would affect the result set.

-- nu_dba
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 12/09/2005 :  16:14:15  Show Profile  Reply with Quote
What count u need ?
Don't u use Group By ?

PS : Its very hard to go thru ur whole Q as it contains all the Insert Statements. It would be much easier if u give some sample data.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/10/2005 :  00:33:57  Show Profile  Reply with Quote

SELECT 
  stype.stypeTitle,
  stud.studFName + ' ' + stud.studLName AS studName,
  stud.studEmail,
  maj.majTitle,
  COUNT(SC.studID) AS [MyCount]	-- Do not use COUNT(*) as that will count NULL results in tblStudentClass
FROM
  tblStudent AS stud
  INNER JOIN tblMajor AS maj ON (stud.majID = maj.majID)
  INNER JOIN tblStudentType AS stype ON (stud.stypeID = stype.stypeID)
  LEFT OUTER JOIN tblStudentClass AS SC ON SC.studID = stud.studID
WHERE stud.studID > 1
GROUP BY
   stype.stypeTitle,
  stud.studFName + ' ' + stud.studLName,
  stud.studEmail,
  maj.majTitle,
  stype.stypeID,
  stud.studLName,
  stud.studFName
ORDER BY  stype.stypeID ASC, stud.studLName ASC, stud.studFName ASC

Should do the trick, note that I have had to add the columns in your ORDER BY to the GROUP BY, otherwise they won't be in "scope".

"Its very hard to go thru ur whole Q as it contains all the Insert Statements. It would be much easier if u give some sample data"

The insert statements are the sample data. DTFan even provided expected results, AND included the edge condition for a student NOT having a class.

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 12/10/2005 :  12:59:23  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Let us know if you need more help DTFan. This was a perfect post with sample data and expected results. We appreciate when people take the time to do this.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/10/2005 :  13:40:53  Show Profile  Reply with Quote
Blimey! I don't often see you type that "MeanOldDBA" - praise indeed.

Kristen
Go to Top of Page

DTFan
Yak Posting Veteran

USA
52 Posts

Posted - 12/12/2005 :  08:16:52  Show Profile  Reply with Quote
First off, sorry about no reply over the weekend. No internet connection at home ... I think I actually started going through withdrawals.

Kristen - Thank you. That worked perfectly. And you were right, I was using Count(*) so even if I had gotten it right (which I hadn't because what you provided was quite a bit above my level), it wouldn't have been right.

derrickleggett and Kristen - thanks for the comments about the post. I was worried that I had done it wrong again.

Once again, this site rocks. I'm editingthe original post to indicate [Resolved]! You gurus have made my day!

DTFan
Ever-hopeful programmer-in-training
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000