Group by with outer / left join in sql server 2008
I have data in 1 table as
UserID CourseID TestID Result
1 1 1 P
1 1 2 P
2 1 1 F
2 1 2 F
shows 2 users (UserID = 1 & 2) accepted Test (TestID = 1 & 2) in CourseID = 1
Now that CourseID has a total of 3 tests (e.g. TestID = 1,2,7) as
CourseID TestID
1 1
1 2
1 7
this means none of the users accepted Test (TestID = 7), now I want to show below data
UserID CourseID TestID Result
1 1 1 P
1 1 2 P
1 1 7 null
2 1 1 F
2 1 2 F
2 1 7 null
I am trying to use outer left / right outer join with Group by but cannot get the desired result. How can this be achieved?
+3
source to share
2 answers
Configuring MS SQL Server 2008 schema :
create table Result
(
UserID int,
CourseID int,
TestID int,
Result char(1)
)
insert into Result values
(1, 1, 1, 'P'),
(1, 1, 2, 'P'),
(2, 1, 1, 'F'),
(2, 1, 2, 'F')
create table Course
(
CourseID int,
TestID int
)
insert into Course values
(1, 1),
(1, 2),
(1, 7)
Request 1 :
select U.UserID,
C.CourseID,
C.TestID,
R.Result
from (
select distinct UserID
from Result
) as U
cross apply Course as C
left outer join Result as R
on R.CourseID = C.CourseID and
R.TestID = C.TestID and
R.UserID = U.UserID
Results :
| USERID | COURSEID | TESTID | RESULT |
---------------------------------------
| 1 | 1 | 1 | P |
| 1 | 1 | 2 | P |
| 1 | 1 | 7 | (null) |
| 2 | 1 | 1 | F |
| 2 | 1 | 2 | F |
| 2 | 1 | 7 | (null) |
Note. If you have a table Users
you can replace the derived table
(
select distinct UserID
from Result
) as U
instead of Users as U
.
+2
source to share