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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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
22859 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
22859 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  
 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.12 seconds. Powered By: Snitz Forums 2000