CODEWITHSUNDEEP
sql-server
aleczandru
aleczandru

Reputation: 5449

Creating a SELECT and JOIN after a UNION

Hi I have a UNION beetween to tables that looks like this:

SELECT GetStudentClassmates.AvatarFileName  as AvatarFileName,
       GetStudentClassmates.UserName        as UserName,
       GetStudentClassmates.RoleId          as RoleId
FROM GetStudentClassmates
WHERE GetStudentClassmates.StudentId = @studentId

UNION

SELECT GetStudentTeachers.AvatarFileName    as AvatarFileName,
       GetStudentTeachers.UserName          as UserName,
       GetStudentTeachers.RoleId            as RoleId
FROM GetStudentTeachers

Now I need to join the result of the UNION with to other tables and add another column to the final result.

This is the JOIN code I am using 

LEFT OUTER JOIN Sync_StudentAssignments as StudentAssignments 
  ON @studentId = StudentAssignments.StudentID 
  AND StudentAssignments.Deletion_Date IS NULL
LEFT OUTER JOIN Sync_StudentAssignmentInstances as StudentAssignmentInstances 
  ON StudentAssignments.StudentAssignmentID = StudentAssignmentInstances.StudentAssignmentID 
  AND StudentAssignmentInstances.Deletion_Date IS NULL

My problem is that I do not know where I have to add the select code , that would look something like this I think:

SELECT StudentAssignmentInstances.ModuleInstanceId

My final table should have the following columns:

AvatarFileName,
UserName,
RoleId,
ModuleInstanceId

How can I achieve this?

Upvotes: 1

Views: 108

Answers (3)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

In SQL Server If you want to join with some union you can use subselect form or CTE. Here I would use CTE because it adds some order in TSQL code...

;WITH Participants AS (
  SELECT GetStudentClassmates.AvatarFileName  as AvatarFileName,
         GetStudentClassmates.UserName        as UserName,
         GetStudentClassmates.RoleId          as RoleId
  FROM GetStudentClassmates
  WHERE GetStudentClassmates.StudentId = @studentId

  UNION ALL

  SELECT GetStudentTeachers.AvatarFileName    as AvatarFileName,
         GetStudentTeachers.UserName          as UserName,
         GetStudentTeachers.RoleId            as RoleId
  FROM GetStudentTeachers)
SELECT
FROM 
  Participants p
  JOIN WhateverYouWant wyw
    ON wyw.{column1} = p.{column1}
    AND wyw.{column2} = p.{column2}
    ...

Here WhateverYouWant is probably your Sync_StudentAssignments JOIN Sync_StudentAssignmentInstances. However, you have joined that using a variable and a const, which would give a cartesian product and I believe that was not your intention.

EDIT

Now I was looking at your conditions more carefully, I believe you have to do the following:

;WITH Participants AS (
  SELECT GetStudentClassmates.AvatarFileName,
         GetStudentClassmates.UserName,
         GetStudentClassmates.RoleId,
         GetStudentClassmates.StudentId       
  FROM GetStudentClassmates
  --WHERE GetStudentClassmates.StudentId = @studentId
  --uncomment previous line only if you need just one student in your recordset

  UNION ALL

  SELECT GetStudentTeachers.AvatarFileName,
         GetStudentTeachers.UserName,
         GetStudentTeachers.RoleId,
         GetStudentClassmates.TeacherId AS StudentId -- just guessing column name
  FROM GetStudentTeachers
  --previous select means you need all of the teachers, no matter which student
)
SELECT
  p.AvatarFileName,
  p.UserName,
  p.RoleId,
  StudentAssignmentInstances.ModuleInstanceId
FROM 
  Participants p
  LEFT JOIN Sync_StudentAssignments as StudentAssignments 
    ON p.StudentId = StudentAssignments.StudentID 
    AND StudentAssignments.Deletion_Date IS NULL
  LEFT JOIN Sync_StudentAssignmentInstances as StudentAssignmentInstances 
    ON StudentAssignments.StudentAssignmentID = StudentAssignmentInstances.StudentAssignmentID 
    AND StudentAssignmentInstances.Deletion_Date IS NULL

Upvotes: 2

Jesper Fyhr Knudsen
Jesper Fyhr Knudsen

Reputation: 7937

SELECT tbl.AvartarFileName, 
       tbl.UserName, 
       tbl.RoleId, 
       StudentAssignmentInstances.ModuleInstanceId
FROM
(
    SELECT GetStudentClassmates.AvatarFileName  as AvatarFileName,
           GetStudentClassmates.UserName        as UserName,
           GetStudentClassmates.RoleId          as RoleId
      FROM GetStudentClassmates
     WHERE GetStudentClassmates.StudentId = @studentId

     UNION

    SELECT GetStudentTeachers.AvatarFileName    as AvatarFileName,
           GetStudentTeachers.UserName          as UserName,
           GetStudentTeachers.RoleId            as RoleId
           FROM GetStudentTeachers
) AS tbl
LEFT OUTER JOIN Sync_StudentAssignments as StudentAssignments 
  ON @studentId = StudentAssignments.StudentID 
  AND StudentAssignments.Deletion_Date IS NULL
LEFT OUTER JOIN Sync_StudentAssignmentInstances as StudentAssignmentInstances 
  ON StudentAssignments.StudentAssignmentID = StudentAssignmentInstances.StudentAssignmentID 
  AND StudentAssignmentInstances.Deletion_Date IS NULL

Upvotes: 1

davek
davek

Reputation: 22915

Wrap your UNION bit in brackets and use it as an in-line table, then join normally:

-----------------------------------------------------------
-- choose the exact columns you need here using the aliases
-----------------------------------------------------------
select x.*, ssa.*, ssai.* from
(
   select ....
   union
   select ....
) as x
inner join Sync_StudentAssignments ssa on ...
inner join Sync_StudentAssignmentInstances ssai on ....

Upvotes: 4

Related Questions