Trying to JOIN

Question

Post reply

Trying to JOIN

Norm Hill-416634

SSC Rookie

Points: 39
More actions
March 30, 2010 at 10:35 am

#217208

I am working on creating Vacation Bible School site using .NET and SQL Server 2005. I have a table with fields FirstName, LastName, ClassName, RegType, RegStatus and Attending2010. I have a second table that contains the ClassName, EnrollLimit and ClassYear.
I am trying to create a query that will result in a dataset that has all 2010 Classes, their Registration Type (Student, Adult, etc), their Registration Status (<< Not Set >>, Registered, WaitList, etc.), the Enrollment Limit of a class and the number of registrants in each class and type.
I have tried many, many variations of the query and still can seem to make it work. Below is the latest:
select c.ClassName,c.EnrollLimit,p.RegType,p.RegStatus,P.Class,
(Select count(*) from Participant t where t.Class = c.ClassName) as NumofReg
from Classes c left outer join Participant p on c.ClassName =
p.Class where c.ClassYear = '2010' and p.Attending2010 = 1
Any ideas would be greatly appreciated.
Regards,
Norm Hill

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

Norm this is just a guess based on adapting your posted query;

does this get you close to what you were after?

SELECT

c.ClassName,

c.EnrollLimit,

p.RegType,

p.RegStatus,

P.Class,

X.NumofReg

FROM Classes c

LEFT OUTER JOIN Participant p

ON c.ClassName = p.Class

--get the count by class as a subquery

LEFT OUTER JOIN (Select Class,count(*) as NumofReg from Participant t GROUP BY Class) X

ON X.Class = c.ClassName

WHERE c.ClassYear = '2010'

AND p.Attending2010 = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95461 More actions · Answer 2

Norm,

If what Lowell suggests doesn't help you out, they you are going to have to help us help you. Please read the first link in my signature, and provide table DDL and sample data in the form of insert statements... and based on that sample data what the expected output should be.

Thanks,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Norm Hill-416634 SSC Rookie Points: 39 More actions · Answer 3

Makes sense WayneS. Here's a go at providing the pertinent information:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- if the Classes Table exists, drop it

IF OBJECT_ID('MyDB..Classes','U') IS NOT NULL

DROP TABLE CLASSES

-- CREATE THE CLASSES TABLE

CREATE TABLE [dbo].[Classes](

[ClassName] [nchar](20) NOT NULL,

[TempID] [int] IDENTITY(1,1) NOT NULL,

[ClassYear] [nchar](4) NULL,

[EnrollLimit] [int] NULL,

[CreatedDate] [datetime] NULL,

[CreatedBy] [nchar](10) NULL,

[ModifiedDate] [datetime] NULL,

[ModifiedBy] [nchar](10) NULL,

[ClassID] [nchar](10) NULL

) ON [PRIMARY]

-- Turn Identity on

SET IDENTITY_INSERT CLASSES ON

-- Insert relevant Data into the classes table

Insert into Classes

(ClassName,ClassYear,EnrollLimit)

SELECT 'Class1','2010',10 UNION ALL

SELECT 'Class2','2009',35 UNION ALL

SELECT 'Class3','2010',10 UNION ALL

SELECT 'Class4','2010',10 UNION ALL

SELECT 'Class5','2010',10

-- Turn identify off

SET IDENTITY_INSERT CLASSES OFF

-- Check for the Participant table, if exists, drop it

IF OBJECT_ID('MyDB..Participant','U') IS NOT NULL

DROP TABLE Participant

-- Create the Particpant table

CREATE TABLE [dbo].[Participant](

[RegType] [nchar](15) NULL,

[Prefix] [nchar](10) NULL,

[FirstName] [nchar](25) NOT NULL,

[MI] [nchar](1) NULL,

[LastName] [nchar](35) NOT NULL,

[Suffix] [nchar](5) NULL,

[PrefName] [nchar](25) NULL,

[Add1] [nchar](35) NULL,

[Add2] [nchar](35) NULL,

[City] [nchar](20) NULL,

[State] [nchar](2) NULL,

[Zip] [nchar](10) NULL,

[Birthdate] [datetime] NULL,

[EmergencyContact] [nchar](35) NULL,

[EmergencyPhone1] [nchar](14) NULL,

[EmergencyPhone2] [nchar](14) NULL,

[EmergencyRelationship] [nchar](20) NULL,

[Parent1] [nchar](35) NULL,

[Parent1Phone1] [nchar](14) NULL,

[Parent1Phone2] [nchar](14) NULL,

[Parent1Relationship] [nchar](20) NULL,

[Parent2] [nchar](35) NULL,

[Parent2Phone1] [nchar](14) NULL,

[Parent2Phone2] [nchar](14) NULL,

[Parent2Relationship] [nchar](20) NULL,

[TShirtSize] [nchar](15) NULL,

[EmailAddress] [nchar](35) NULL,

[LastGrade] [nchar](12) NULL,

[CreatedDate] [datetime] NULL,

[CreatedBy] [nchar](10) NULL,

[ModifiedDate] [datetime] NULL,

[ModifiedBy] [nchar](10) NULL,

[ConfirmEmailDate] [datetime] NULL,

[Class] [nchar](20) NULL,

[RegStatus] [nchar](10) NULL,

[RegNum] [nchar](10) NULL,

[PrevRegNum] [nchar](10) NULL,

[VBSBuddy] [nchar](35) NULL,

[Allergy] [nchar](255) NULL,

[Attending2010] [bit] NULL,

[Attending2009] [bit] NULL,

[Attending2008] [bit] NULL

) ON [PRIMARY]

-- Insert relevant Data into the Participant table

INSERT INTO PARTICPANT

(RegType, FirstName,Class,RegStatus,Attending2010)

SELECT 'STUDENT','STUDENT1','Class1','<< Not Set >>',1 UNION ALL

SELECT 'STUDENT','STUDENT2','Class1','<< Not Set >>',1 UNION ALL

SELECT 'STUDENT','STUDENT3','Class2','Registered',1 UNION ALL

SELECT 'TEACHER','TEACHER1','Class1','<< Not Set >>',1 UNION ALL

SELECT 'TEACHER','TEACHER3','Class2','Registered',1 UNION ALL

SELECT 'STUDENT','STUDENT4','Class5','Confirmed',0 UNION ALL

SELECT 'STUDEMT','STUDENT5','Class1','Waitlist',1 UNION ALL

SELECT 'STUDENT','STUDENT6','Class2','Confirmed',1 UNION ALL

SELECT 'STUDENT','STUDENT7','Class3','<< Not Set >>',1 UNION ALL

SELECT 'STUDENT','STUDENT8','Class4','<< Not Set >>',1

Using the code provided by Lowell, the the result was the same as I was getting:

ClassName EnrollLimit RegType RegStatus Class NumofReg

<< Not Set >> 0 Student Registered << Not Set >> 8

What I am looking for is the ClassName, EnrollLimit and NumofReg to have

the real values. For the lack of a better description, basically the output would look like a report with SubTotal fields and no detail. Does this help explain what I am looking for?

Thanks,

Norm Hill

K Cline Ten Centuries Points: 1391 More actions · Answer 4

Not exactly sure what you mean. Based off of your create script and Lowell's selection script I was getting expected results.

ClassName,EnrollLimit,RegType,RegStatus,Class,NumofReg

Class1,10,STUDENT,<< Not Set >>,Class1,4

Class1,10,TEACHER,<< Not Set >>,Class1,4

Class1,10,STUDEMT,Waitlist,Class1,4

Class3,10,STUDENT,<< Not Set >>,Class3,1

Class4,10,STUDENT,<< Not Set >>,Class4,1

Resubmitting Lowell's query with a small change that also displays classes with zero registrations (Class5 from your sample data).

Select C.ClassName, C.EnrollLimit, COALESCE(P.RegType, '') As [RegType],

COALESCE(P.RegStatus, '') As [RegStatus], COALESCE(SubQuery.NumofReg, 0) As [NumOfReg]

From Classes As C

Left Join Participant As P On C.ClassName = P.Class And P.Attending2010 = 1

-- Get the count by class as a subquery

Left Join

(

Select Class, COUNT(*) As [NumofReg]

From Participant

Where (Attending2010 = 1)

Group By Class

) As SubQuery On SubQuery.Class = C.ClassName

Where (C.ClassYear = '2010');

Go

ClassName,EnrollLimit,RegType,RegStatus,NumOfReg

Class1,10,STUDENT,<< Not Set >>,4

Class1,10,TEACHER,<< Not Set >>,4

Class1,10,STUDEMT,Waitlist,4

Class3,10,STUDENT,<< Not Set >>,1

Class4,10,STUDENT,<< Not Set >>,1

Class5,10,,,0

Norm Hill-416634 SSC Rookie Points: 39 More actions · Answer 5

K. Cline,

Thanks for responding but the issue is that WaitList for Class1 should have a 1 not 4 and Class1 should have only 2 STUDENTs as Status '<< Not Set >>'. I think that is what the script is tripping up on, the number I am looking for qty for each class for each RegType for each Registration status.

Thanks for your assistance.

Norm Hill

ClassName,EnrollLimit,RegType,RegStatus,NumOfReg

Class1,10,STUDENT,<< Not Set >>,4

Class1,10,TEACHER,<< Not Set >>,4

Class1,10,STUDEMT,Waitlist,4

Class3,10,STUDENT,<< Not Set >>,1

Class4,10,STUDENT,<< Not Set >>,1

Class5,10,,,0

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Okay, I am getting a bit confused. Based on the sample data provided, what is the results set you are expecting to get?

Norm Hill-416634 SSC Rookie Points: 39 More actions · Answer 7

Lynn,

Thanks for bearing with me.

My Data:

SELECT 'STUDENT','STUDENT1','Class1','<< Not Set >>',1 UNION ALL

SELECT 'STUDENT','STUDENT2','Class1','<< Not Set >>',1 UNION ALL

SELECT 'STUDENT','STUDENT3','Class2','Registered',1 UNION ALL

SELECT 'TEACHER','TEACHER1','Class1','<< Not Set >>',1 UNION ALL

SELECT 'TEACHER','TEACHER3','Class2','Registered',1 UNION ALL

SELECT 'STUDENT','STUDENT4','Class5','Confirmed',0 UNION ALL

SELECT 'STUDEMT','STUDENT5','Class1','Waitlist',1 UNION ALL

SELECT 'STUDENT','STUDENT6','Class2','Confirmed',1 UNION ALL

SELECT 'STUDENT','STUDENT7','Class3','<< Not Set >>',1 UNION ALL

SELECT 'STUDENT','STUDENT8','Class4','<< Not Set >>',1

I would like it to come out

Class Name RegType RegStatus Num of Registrants EnrollLimit

Class1 Student << Not Set >> 2 10

Class1 Student WaitList 1 10

Class1 Teacher << Not Set >> 1 10

Class3 Student << Not Set >> 1 10

Class4 Student << Not Set >> 1 10

Class5 Null Null Null 10

Does that make any sense?

Regards,

Norm Hill

K Cline Ten Centuries Points: 1391 More actions · Answer 8

Ok. I think I finally understand the full picture.

Select C.ClassName, SubQuery.RegType, SubQuery.RegStatus,

COALESCE(SubQuery.NumofReg, 0) As [NumOfReg], C.EnrollLimit

From Classes As C

Left Join

(

Select Class, RegType, RegStatus, COUNT(*) As [NumofReg]

From Participant

Where (Attending2010 = 1)

Group By Class, RegType, RegStatus

) As SubQuery On SubQuery.Class = C.ClassName

Where (C.ClassYear = '2010')

Order By C.ClassName, SubQuery.RegType, SubQuery.RegStatus;

Go

Norm Hill-416634 SSC Rookie Points: 39 More actions · Answer 9

I am not worthy!:-D Worked like a charm. Thank you very much!

Norm Hill

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

See what happens when you provide both sample data AND expected results based on the sample data?

That's why we ask for all the info we do, to help you.