February 8, 2011 at 2:00 pm
I am using the RIGHT OUTER JOIN to try and bring back a list of ALL Grades from a lookup table and a count of students for the grades. In this scenario, not all students for a particular set of data will have a match to every possible grade value and therefore should have NULL in the count next to that grade from the lookup table.
SELECT COUNT(StudentID) StudentCount, GradeLookupName
FROM Student s
RIGHT OUTER JOIN GradeLookupTable g ON s.GradeID = g.GradeID
should return:
StudentCount GradeLookupName
2 A
5 B
NULL C
3 D
NULL F
Am I missing something? I am actually using a Table Value Function for the GradeLookupTable because we have an Enum table with many different lookup values and the TVF returns a table with only the list of Grades as it is used (I checked to make sure). I have also tried:
SELECT COUNT(StudentID) StudentCount, GradeLookupName
FROM Student s
CROSS JOIN GradeLookupTable g ON s.GradeID = g.GradeID
and that didn't work either. Any ideas?
Thanks, Scott
February 8, 2011 at 7:36 pm
CREATE TABLE GradeLookupTable
(GradeID INTEGER
,GradeLookupName NCHAR(1)
,CONSTRAINT pk_Grade PRIMARY KEY (GradeID)
)
go
INSERT INTO GradeLookupTable
SELECT 1, 'A' UNION
SELECT 2, 'B' UNION
SELECT 3, 'C' UNION
SELECT 4, 'D' UNION
SELECT 5, 'F'
go
CREATE TABLE Student
(StudentID INTEGER NOT NULL
,GradeID INTEGER NOT NULL
,CONSTRAINT pk_Student PRIMARY KEY (StudentID)
,CONSTRAINT fk_Student_Grade
FOREIGN KEY (GradeID)
REFERENCES GradeLookupTable (GradeID)
)
go
INSERT INTO Student
(StudentID
,GradeID)
SELECT 1, 1 UNION
SELECT 2, 1 UNION
SELECT 3, 2 UNION
SELECT 4, 2 UNION
SELECT 5, 2 UNION
SELECT 6, 2 UNION
SELECT 7, 2 UNION
SELECT 8, 4 UNION
SELECT 9, 4 UNION
SELECT 10, 4
go
SELECT COUNT(StudentID) StudentCount, GradeLookupName
FROM Student s
RIGHT OUTER JOIN
GradeLookupTable g
ON s.GradeID = g.GradeID
GROUP BY GradeLookupName
I only had to add...
GROUP BY GradeLookupName
...to your query to get it to produce the results you wanted. Without the GROUP BY, an error is returned.
StudentCountGradeLookupName
2 A
5 B
0 C
3 D
0 F
The query you gave showed that GradeID was a foreign key into Student from GradeLookupTable. My apologies, but I think it is odd to be recording grade information in a Student table (even a summary grade). Normally I would expect to have at least the following tables:
Students (information about students)
Grades (information about grades)
Classes (information about classes)
StudentClassGrades (information about which students received which grades for which classes)
February 9, 2011 at 8:09 am
Thanks for the response. This database receives information from many different and very dirty sources. I hear ya on normalization. Each student is a one-off so it is what it is. I figured out that my problem has been using the WHERE clause in my queries instead of using multiple ON conditions to join my tables. This is working for me now. I appreciate your input.
Squatt.
February 10, 2011 at 10:43 am
"Drop your absurd “GradeLookupTable” which violates ISO-11179 rules by having “table” in a table name. I am also surprised you had one student and not more – that is what your table name says. It also does not model a student – it is a grade book! "
Celko, if only most DBA's had the authority to set and enforce standards in most software development groups. You think the above is obsurd??/ ... you should see some the crap that passes for software and/or database object design in many places (including here). Most of the time the DBA's have to live with it and are offered no input to new designs (despite years of software and relational database architecture experience).
So make suggestions but unless you know that the "obsurdity" is a creation of and by the poster please refrain from preaching to the choir.
The probability of survival is inversely proportional to the angle of arrival.
February 10, 2011 at 11:55 am
Thanks all for the responses. Let me take a moment to say, I posted a generic example with "absurd" naming to give folks a feel for the data without placing my table schema into the post. I appreciate that we are all looking out for the little man and trying to right the wrongs that are out there in database land.
That being said, we do have a well thought out schema for the data that we are storing based on the data we receive from our external sources. Unfortunately, as I'm sure you all have noticed, not all external sources have a really good grip on data modeling.
So, if anyone has an answer to this question, "how do you return all records from the right side of a join and only the matches from the base table that are present for a subset of data when joining multiple tables together with a few constraints", I am all ears.
I have tried:
SELECT COUNT(Table1.Field1) AS MyCount, Table2.Field1 FieldA, Table3.Field1 FieldB
FROM Table1 t1
RIGHT OUTER JOIN Table2 t2 ON t1.KeyField = t2.KeyField
JOIN Table3 t3 ON t3.KeyField2 = t1.KeyField2
AND t3.CriteriaField1 = @Criteria1
AND t3.CriteriaField2 = @Criteria2
I would like to return all values from Table2 while receiving a count of matches from Table1 and 0 for the non-matches. So given the scenario that with the predicate values bringing 4 matches from Table1 where there are 6 values in Table2 the results would be:
MyCount FieldA FieldB
2 Value1 Value1
4 Value2 Value2
3 Value3 Value3
9 Value4 Value4
0 Value5 Value5
0 Value6 Value6
Hope that doesn't offend anyone and is more clear what I am trying to accomplish.
Thanks.
February 10, 2011 at 12:11 pm
Okay, now I understand what you want to get. Try it this way:
SELECT SUM(case when Table1.Field1 is NULL then 0 else 1 end) AS MyCount, Table2.Field1 FieldA, Table3.Field1 FieldB
FROM Table1 t1
RIGHT OUTER JOIN Table2 t2 ON t1.KeyField = t2.KeyField
JOIN Table3 t3 ON t3.KeyField2 = t1.KeyField2
AND t3.CriteriaField1 = @Criteria1
AND t3.CriteriaField2 = @Criteria2
group by Table2.Field1, Table3.Field1
The probability of survival is inversely proportional to the angle of arrival.
February 10, 2011 at 1:10 pm
OK, here is the an example with table names and obvious foreign key relationships. It contains data and the query I really need to fix. I am still not getting the expected values from the RIGHT OUTER JOIN. I didn't add Primary keys and actual Foreign Keys for you but you should get the idea.
Thanks.
USE [MyTest]
GO
/****** Object: Table [dbo].[Course] Script Date: 02/10/2011 15:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course](
[Course_ID] [int] NULL,
[TrainingSite_ID] [int] NULL,
[Start_Date] [datetime] NULL,
[Course_Name] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Student] Script Date: 02/10/2011 15:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
[Student_ID] [int] NULL,
[Course_ID] [int] NULL,
[Grade_ID] [int] NULL,
[First_Name] [nvarchar](50) NULL,
[Last_Name] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Grade] Script Date: 02/10/2011 15:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Grade](
[Grade_ID] [int] NULL,
[Grade_Letter] [char](1) NULL,
[Grade_Description] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-- Student
INSERT INTO Student (Student_ID, Course_ID, Grade_ID, First_Name, Last_Name) Values (1,1,1,'Joe','Blow')
INSERT INTO Student (Student_ID, Course_ID, Grade_ID, First_Name, Last_Name) Values (2,1,1,'George','Jefferson')
INSERT INTO Student (Student_ID, Course_ID, Grade_ID, First_Name, Last_Name) Values (3,1,3,'Michael','Jackson')
INSERT INTO Student (Student_ID, Course_ID, Grade_ID, First_Name, Last_Name) Values (4,1,2,'Fred','Flintstone')
INSERT INTO Student (Student_ID, Course_ID, Grade_ID, First_Name, Last_Name) Values (5,1,3,'Knights','Nee')
INSERT INTO Student (Student_ID, Course_ID, Grade_ID, First_Name, Last_Name) Values (6,1,1,'Harley','Davidson')
GO
-- Grade
INSERT INTO Grade (Grade_ID, Grade_Letter, Grade_Description) Values (1,'A','Excellent')
INSERT INTO Grade (Grade_ID, Grade_Letter, Grade_Description) Values (2,'B','Good')
INSERT INTO Grade (Grade_ID, Grade_Letter, Grade_Description) Values (3,'C','Fair')
INSERT INTO Grade (Grade_ID, Grade_Letter, Grade_Description) Values (4,'D','Poor')
INSERT INTO Grade (Grade_ID, Grade_Letter, Grade_Description) Values (6,'F','Fail')
GO
-- Course
INSERT INTO Course (Course_ID, TrainingSite_ID, Start_Date, Course_Name) Values (1,1,'1/1/2010','Course1')
INSERT INTO Course (Course_ID, TrainingSite_ID, Start_Date, Course_Name) Values (2,2,'1/2/2010','Course2')
GO
DECLARE @Course_ID int, @TrainingSite_ID int, @Start_Date datetime, @End_Date datetime
SET@Course_ID = 1
SET @TrainingSite_ID = 1
SET@Start_Date = '1/1/2010'
SET@End_Date = '1/31/2010'
SELECTSUM(CASE WHEN Student_ID IS NULL THEN 0 ELSE 1 END) AS StudentCount, Grade_Description
FROMStudent s
RIGHT OUTER JOIN Grade g ON s.Grade_ID = g.Grade_ID
JOINCourse c ON c.Course_ID = s.Course_ID
ANDc.TrainingSite_ID = @TrainingSite_ID
ANDc.Start_Date BETWEEN @Start_Date AND @End_Date
GROUP BY Grade_Description
February 10, 2011 at 1:21 pm
you need a left join on the Course table like this:
SELECT SUM(CASE WHEN Student_ID IS NULL THEN 0 ELSE 1 END) AS StudentCount, Grade_Description
FROM Student s
RIGHT OUTER JOIN Grade g ON s.Grade_ID = g.Grade_ID
LEFT JOIN Course c ON c.Course_ID = s.Course_ID
AND c.TrainingSite_ID = @TrainingSite_ID
AND c.Start_Date BETWEEN @Start_Date AND @End_Date
GROUP BY Grade_Description
The probability of survival is inversely proportional to the angle of arrival.
February 10, 2011 at 1:51 pm
To get rid of the extra work of the case and sum, another option:
SELECT COUNT(Student_ID)
, Grade_Description
FROM Student s
RIGHT OUTER JOIN Grade g
ON s.Grade_ID = g.Grade_ID
LEFT JOIN Course c
ON c.Course_ID = s.Course_ID
AND c.TrainingSite_ID = @TrainingSite_ID
AND c.Start_Date BETWEEN @Start_Date AND @End_Date
GROUP BY Grade_Description
-- Cory
February 10, 2011 at 2:14 pm
OK, this can be closed now. Thanks to all for your input. I finally reversed the Grade and Course tables in the joins and got my answer. I used the CASE statement that was suggested which helped out. It has been a long time since I had to mess around with the OUTER JOINs and so I am glad this came up to refresh my mind.
Thanks!
February 10, 2011 at 3:07 pm
those "ISO-11179 rules" aren't rules, they are guide lines, or at least an attempt at best practices; calling them anything other than is misleading.
no DBMS system anywhere prevents you from doing your work if the objects don't follow the naming conventions you identified here.
Many of the forum posters here are just starting out, and need guidance and support, not ridicule.
I'd really like to see you change the style of your posts to be less abrasive. Maybe I read them wrong, but it always seems you go out of your way to post aggressively when it's not needed.
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply