RIGHT OUTER JOIN not returning correct data

  • 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

  • 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)

  • 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.

  • "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.

  • 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.

  • 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.

  • 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

  • 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.

  • 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

  • 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!

  • 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


    --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!

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

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