October 1, 2012 at 1:14 pm
I've got two tables Curriculum and UserCurriculum. I want to list all classes for each user and show nulls where the user hasn't taken the class.
[LMS].[Curriculum]
[CurriculumKey] [int] IDENTITY(1,1) NOT NULL,
[CurriculumName] [varchar](255) NOT NULL,
[CustomerServiceCourse] [bit] NULL,
[FranchiseCourse] [bit] NULL,
[ManagerCourse] [bit] NULL,
[CurriculumType] [varchar](25) NULL
[LMS].[UserCurriculum]
[Username] [varchar](255) NOT NULL,
[CurriculumKey] [int] NOT NULL,
[CompletionDateKey] [int] NOT NULL,
[FranchiseKey] [int] NOT NULL
Sounds simple enough; do a left join like:
SELECT c.*,
uc.*
FROM LMS.Curriculum c
LEFT OUTER JOIN LMS.UserCurriculum uc ON c.CurriculumKey = uc.CurriculumKey
ORDER BY CompletionDateKey;
But this returns back just the number of rows in the UserCurriculum.
What obvious thing am I missing?
Thanks,
Rob
October 1, 2012 at 1:19 pm
Have you verified that there are rows that should show no-join to that table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2012 at 1:24 pm
Yes, I can have one particular test user that has taken 9 of the 15 classes from the Curriculum table.
October 1, 2012 at 1:32 pm
The query is backwards for that. The left join you defined would find a curriculum that has never had any users, not the other way around.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2012 at 1:44 pm
Okay, but if I switch to a right join I still get the total number of rows from the UserCurriculum table.
SELECT c.*,
uc.*
FROM LMS.Curriculum c
RIGHT OUTER JOIN LMS.UserCurriculum uc ON c.CurriculumKey = uc.CurriculumKey
ORDER BY CompletionDateKey;
Thinking outloud here: So the first select returned back all rows because each class has at least one user that has taken the class. The second select returned back all rows because each user had taken at least one class.
I probably didn't explain what I was after, but I'm looking for users that haven't taken all (15) of the classes from the Curriculum table.
So my first thought is get a cartesian product with a full outer join and then look for Null Curriculum keys.
SELECT c.*,
uc.*
FROM LMS.Curriculum c
FULL OUTER JOIN LMS.UserCurriculum uc ON c.CurriculumKey = uc.CurriculumKey
ORDER BY CompletionDateKey;
But that still gives me the same count as the left and right outer joins. Hmmmm.
October 1, 2012 at 2:04 pm
Do you have a Users table? If so, you need to start from that, then join to UsersCurriculum and then Curriculum.
Otherwise, you don't have a full list of users.
If you want all users joined to all curriculae, then it's a Cross Join from Users to Curriculum, then an outer join from there to UsersCurriculum. That will give you all Users, all Curiculums, and a Null if they've never done it, or a value if they have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2012 at 2:25 pm
Thank you for breaking that down for me. No users table, but I can do:
;WITH CTE_Users (Username)
AS
(
SELECT DISTINCT Username
FROM LMS.UserCurriculum
)
SELECT c.*,
u.*,
uc.*
FROM LMS.Curriculum c
CROSS JOIN CTE_Users u
LEFT JOIN LMS.UserCurriculum uc ON uc.Username = u.Username AND uc.CurriculumKey = c.CurriculumKey
WHERE u.Username = 'TestUser' AND uc.Username IS NULL
ORDER BY u.Username, c.CurriculumKey;
Many thanks!! That helps a lot.
Rob
October 1, 2012 at 3:12 pm
You have an ORDER BY clause with a field from the joined table. Since ORDER BY can't evaluate null, I think this is making it an inner join.
I'd leave the ORDER BY off and let whatever application is receiving the data do the sorting.
October 1, 2012 at 5:32 pm
Here's another way:select uc.UserName, uc.CurriculumKey -- Select the user's...
from UserCurriculum uc
where exists ( -- ...where you can't find a missing Curriculum
select *
from Curriculum c1
left outer join
UserCurriculum uc1
onc1.CurriculumKey = uc1.CurriculumKey
and uc.UserName = uc1.Username
where
uc1.CurriculumKey is Null
)
October 2, 2012 at 8:37 am
dan-572483 (10/1/2012)
You have an ORDER BY clause with a field from the joined table. Since ORDER BY can't evaluate null, I think this is making it an inner join.
Dan, I'm not sure what you mean about the ORDER BY not evaluating Nulls -- ORDER BY doesn't change in any way the number of rows getting returned. Nulls just sort to the top.
[font="Courier New"]ASC | DESC
Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Null values are treated as the lowest possible values.[/font]
October 2, 2012 at 8:44 am
dan-572483 (10/1/2012)
You have an ORDER BY clause with a field from the joined table. Since ORDER BY can't evaluate null, I think this is making it an inner join.I'd leave the ORDER BY off and let whatever application is receiving the data do the sorting.
Not applicable. Outer-table columns in the Where clause can sometimes make an outer join into an inner join, but Order By will just sort NULL first.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 2, 2012 at 8:45 am
Steve, that doesn't work as every class in the Curriculum table has been taken by at least one user. So the inner SELECT of:
select *
from LMS.Curriculum c1
left outer join
LMS.UserCurriculum uc1
on c1.CurriculumKey = uc1.CurriculumKey
and uc1.UserName = uc1.Username
where uc1.CurriculumKey is Null
always returns back zero rows. So there are no records for the outer select to exist in.
The cross join of all users against all classes (Curriculum) above in the thread did what I was after (Users who had not taken all 15 of the classes in the curriculum).
Thanks,
Rob
October 2, 2012 at 9:57 am
I think this does what you want - ie shows users who haven't taken all curriculums, & a list of curriculums they haven't taken:
--========== TEST DATA ======================
if object_id('[dbo].[Curriculum]') is not null drop table [dbo].[Curriculum];
create table [dbo].[Curriculum]
(
[CurriculumKey] [int], -- IDENTITY(1,1) NOT NULL,
[CurriculumName] [varchar](255) NOT NULL,
[CustomerServiceCourse] [bit] NULL,
[FranchiseCourse] [bit] NULL,
[ManagerCourse] [bit] NULL,
[CurriculumType] [varchar](25) NULL
);
insert [dbo].[Curriculum] values ( 1, 'Maths', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 2, 'French', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 3, 'Science', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 4, 'Physics', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 5, 'German', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 6, 'English', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 7, 'Woodwork', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 8, 'Italian', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 9, 'sss', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 10, 'xxx', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 11, 'ccc', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 12, 'eee', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 13, 'fff', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 14, 'ttt', 0, 0, 0, 'X' );
insert [dbo].[Curriculum] values ( 15, 'hhhh', 0, 0, 0, 'X' );
if object_id('[dbo].[UserCurriculum]') is not null drop table [dbo].[UserCurriculum];
create table [dbo].[UserCurriculum]
(
[Username] [varchar](255) NOT NULL,
[CurriculumKey] [int] NOT NULL,
[CompletionDateKey] [int] NOT NULL,
[FranchiseKey] [int] NOT NULL
);
insert [dbo].[UserCurriculum] values ( 'Fred', 4, 3, 0 );
insert [dbo].[UserCurriculum] values ( 'Jim', 4, 3, 0 );
insert [dbo].[UserCurriculum] values ( 'Jim', 2, 5, 0 );
insert [dbo].[UserCurriculum] values ( 'Jim', 7, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 1, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 2, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 3, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 4, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 5, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 6, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 7, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 8, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 9, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 10, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 11, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 12, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 13, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 14, 2, 0 );
insert [dbo].[UserCurriculum] values ( 'Alf', 15, 2, 0 );
--======= EXAMPLE: SHOW USERS WHO HAVEN'T TAKEN ALL CLASSES =============================
select [Username], COUNT(*) as UserCount, MAX(x.CurrCount) as CurrCount
from [dbo].[UserCurriculum]
cross apply (select CurrCount = count(*) from [dbo].[Curriculum]) x
group by [Username]
having COUNT(*) <> MAX(x.CurrCount);
--======= EXAMPLE: LIST USERS/CLASSES NOT TAKEN =============================
with USERS as
(
select distinct Username
from dbo.UserCurriculum
)
select USR.UserName, CURR.CurriculumKey, UC.CurriculumKey
from USERS USR
cross join dbo.Curriculum CURR
left outer join dbo.UserCurriculum UC ON UC.Username = USR.Username AND UC.CurriculumKey = CURR.CurriculumKey
--where UC.CurriculumKey IS NULL; -- Use to exclude taken
October 2, 2012 at 5:59 pm
Laurie, Yep -- very similar to what I did end up doing upstream in the thread.
Thanks,
Rob
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply