Author |
Topic |
DTFan
Yak Posting Veteran
52 Posts |
Posted - 2005-12-09 : 14:05:20
|
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]GOEXEC sp_addextendedproperty 'MS_Description', N'major ID foreign key', 'user', 'dbo', 'table', 'tblStudent', 'column', 'majID'GOEXEC sp_addextendedproperty 'MS_Description', N'student type', 'user', 'dbo', 'table', 'tblStudent', 'column', 'stypeID'GOEXEC sp_addextendedproperty 'MS_Description', N'students first name', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studFName'GOEXEC sp_addextendedproperty 'MS_Description', N'students last name', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studLName'GOEXEC sp_addextendedproperty 'MS_Description', N'students email address', 'user', 'dbo', 'table', 'tblStudent', 'column', 'studEmail'GOCREATE 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]GOEXEC sp_addextendedproperty 'MS_Description', N'what department is this major a part of?', 'user', 'dbo', 'table', 'tblMajor', 'column', 'depID'GOEXEC sp_addextendedproperty 'MS_Description', N'name of the major?', 'user', 'dbo', 'table', 'tblMajor', 'column', 'majTitle'GOCREATE 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]GOEXEC sp_addextendedproperty 'MS_Description', N'students ID (foreign key)', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'studID'GOEXEC sp_addextendedproperty 'MS_Description', N'tblclass foreign key', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'claID'GOEXEC sp_addextendedproperty 'MS_Description', N'f/k to professor table', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'profID'GOEXEC sp_addextendedproperty 'MS_Description', N'what schedule is this class on (f/k to tblSchedule)', 'user', 'dbo', 'table', 'tblStudentClass', 'column', 'schedID'GOCREATE TABLE [tblStudentType] ( [stypeID] tinyint IDENTITY(1, 1) NOT NULL, [stypeTitle] varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL)ON [PRIMARY]GOSET IDENTITY_INSERT [tblMajor] ONGOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (1, 1, 'Temp Major')GOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (2, 3, 'Physics')GOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (3, 2, 'American Lit')GOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (4, 10, 'European Civilization')GOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (5, 9, 'Political Science')GOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (6, 2, '18th Century Poetry')GOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (7, 4, 'Sports Physiology')GOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (8, 8, 'Drama')GOINSERT INTO [tblMajor] ([majID], [depID], [majTitle])VALUES (9, 10, 'American History')GOSET IDENTITY_INSERT [tblMajor] OFFGOCOMMITGOSET IDENTITY_INSERT [tblStudent] ONGOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (1, 1, 1, 'Temp', 'Student', 'no@no.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (2, 9, 1, 'Ali', 'Larter', 'ali@finaldestination.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (3, 9, 1, 'Amy', 'Smart', 'amy@butterflyeffect.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (4, 8, 2, 'Katie', 'Holmes', 'katie@thecreek.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (5, 9, 1, 'Jordana', 'Brewster', 'jordana@fastandfurious.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (6, 7, 3, 'Anna', 'Kournikova', 'anna@tennis.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (7, 2, 2, 'Rhona', 'Mitra', 'rhona@niptuck.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (8, 5, 1, 'Julie', 'Bowen', 'jbowen@bostonlegal.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (9, 2, 4, 'Lake', 'Bell', 'lakeb@surface.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (10, 4, 3, 'Keira', 'Knightly', 'keira@thejacket.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (11, 7, 2, 'Jennifer', 'Morrison', 'jen@house.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (12, 8, 3, 'Evangeline', 'Lilly', 'elilly@lost.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (13, 9, 4, 'Jennifer', 'Finnigan', 'jennifer@closetohome.com')GOINSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], [studLName], [studEmail])VALUES (14, 6, 2, 'Mila', 'Kunis', 'mila@that70sshow.com')GOSET IDENTITY_INSERT [tblStudent] OFFGOCOMMITGOSET IDENTITY_INSERT [tblStudentClass] ONGOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (1, 1, 1, 1, 1)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (2, 2, 22, 11, 4)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (3, 2, 4, 34, 14)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (4, 10, 9, 17, 5)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (5, 5, 4, 34, 14)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (6, 5, 20, 11, 12)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (7, 5, 21, 23, 35)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (8, 2, 21, 23, 35)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (9, 8, 18, 21, 5)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (10, 8, 19, 72, 27)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (11, 8, 9, 17, 5)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (12, 12, 23, 33, 10)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (13, 12, 8, 91, 19)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (14, 9, 2, 64, 9)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (15, 9, 14, 6, 15)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (16, 11, 7, 51, 9)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (17, 2, 20, 11, 12)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (18, 14, 17, 81, 10)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (19, 14, 15, 40, 4)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (20, 3, 20, 11, 12)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (21, 13, 20, 11, 12)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (22, 13, 4, 34, 14)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (23, 4, 19, 72, 27)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (24, 3, 14, 6, 15)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (25, 3, 11, 17, 8)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (26, 3, 22, 11, 4)GOINSERT INTO [tblStudentClass] ([scID], [studID], [claID], [profID], [schedID])VALUES (27, 3, 18, 21, 5)GOSET IDENTITY_INSERT [tblStudentClass] OFFGOCOMMITGOSET IDENTITY_INSERT [tblStudentType] ONGOINSERT INTO [tblStudentType] ([stypeID], [stypeTitle])VALUES (1, 'Full Time')GOINSERT INTO [tblStudentType] ([stypeID], [stypeTitle])VALUES (2, 'Part Time')GOINSERT INTO [tblStudentType] ([stypeID], [stypeTitle])VALUES (3, 'Graduate')GOINSERT INTO [tblStudentType] ([stypeID], [stypeTitle])VALUES (4, 'Evening')GOSET IDENTITY_INSERT [tblStudentType] OFFGOCOMMITGOALTER TABLE [dbo].[tblStudent]ADD CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED ([studID])ON [PRIMARY]GOALTER TABLE [dbo].[tblMajor]ADD CONSTRAINT [PK_tblMajor] PRIMARY KEY CLUSTERED ([majID])ON [PRIMARY]GOALTER TABLE [dbo].[tblStudentClass]ADD CONSTRAINT [PK_tblStudentClass] PRIMARY KEY CLUSTERED ([scID])ON [PRIMARY]GOALTER 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.majTitleFROM 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 > 1ORDER 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 | 3Full Time | Jordana Brewster | emailaddresshere | American History | 3Full Time | Ali Larter | emailaddresshere | American History | 4Full Time | Amy Smart | emailaddresshere | American History | 5Part Time | Katie Holmes | emailaddresshere | Drama | 1Part Time | Mila Kunis | emailaddresshere | 18th Century Poetry | 2Part Time | Rhona Mitra | emailaddresshere | Physics | 0Part Time | Jennifer Morrison | emailaddresshere | Sports Physiology | 1Graduate | Keira Knightly | emailaddresshere | European Civilization | 1Graduate | Anna Kournikova | emailaddresshere | Sports Physiology | 0Graduate | Evangeline Lilly | emailaddresshere | Drama | 2Evening | Lake Bell | emailaddresshere | Physics | 2Evening | 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.DTFanEver-hopeful programmer-in-training |
|
nu_dba
Starting Member
8 Posts |
Posted - 2005-12-09 : 15:17:58
|
When you tried:Anyway, I've tried including a Count(tblStudentClass.studID), but all it returns are 1's for each persondid 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 |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2005-12-09 : 16:14:15
|
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. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-10 : 00:33:57
|
[code]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 tblStudentClassFROM 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.studIDWHERE stud.studID > 1GROUP BY stype.stypeTitle, stud.studFName + ' ' + stud.studLName, stud.studEmail, maj.majTitle, stype.stypeID, stud.studLName, stud.studFNameORDER BY stype.stypeID ASC, stud.studLName ASC, stud.studFName ASC[/code]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 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-10 : 12:59:23
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-10 : 13:40:53
|
Blimey! I don't often see you type that "MeanOldDBA" - praise indeed.Kristen |
|
|
DTFan
Yak Posting Veteran
52 Posts |
Posted - 2005-12-12 : 08:16:52
|
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! DTFanEver-hopeful programmer-in-training |
|
|
|
|
|