CODEWITHSUNDEEP
sqlsql-server
morgan9999
morgan9999

Reputation: 801

How to use join in SQL

I am a beginner with SQL query, I can't quite understand how join works

Here is my SQL query

SELECT 
    CAST (t_Users.UserID AS INT) AS UserID,
    t_Users.FirstName, 
    t_Users.LastName, 
    t_Users.Email,
    t_Users_In_Project.ProjectID,
    t_Teachers.TeacherID,
    t_Teachers.FirstName + ' ' + t_Teachers.LastName AS TeacherName,
    t_Teachers.Email AS TeacherEmail
FROM t_Users
LEFT JOIN t_Users_In_Project
ON t_Users_In_Project.UserID = t_Users.UserID
INNER JOIN t_Projects
ON t_Projects.ProjectID = t_Users_In_Project.ProjectID
LEFT JOIN t_Teachers
ON t_Teachers.TeacherID = t_Projects.TeacherID
WHERE t_Users_In_Project.ProjectID IS NOT NULL
ORDER BY t_Users.UserID ASC

And I got this results

+--------+-----------+----------+-------------+-----------+-----------+-------------+--------------+
| UserID | FirstName | LastName |    Email    | ProjectID | TeacherID | TeacherName | TeacherEmail |
+--------+-----------+----------+-------------+-----------+-----------+-------------+--------------+
|    284 | Person    | A        | personA.com |      1951 |        94 | Teacher A   | teacherA.com |
|    284 | Person    | A        | personA.com |       245 |        68 | Teacher B   | teacherB.com |
|    284 | Person    | A        | personA.com |       139 |        41 | Teacher C   | teacherC.com |
|    284 | Person    | A        | personA.com |       176 |        41 | Teacher C   | teacherC.com |
+--------+-----------+----------+-------------+-----------+-----------+-------------+--------------+

Meanwhile what I want is something like

+--------+-----------+----------+-------------+-----------+-----------+-------------+--------------+
| UserID | FirstName | LastName |    Email    | ProjectID | TeacherID | TeacherName | TeacherEmail |
+--------+-----------+----------+-------------+-----------+-----------+-------------+--------------+
|    284 | Person    | A        | personA.com |      1951 |        94 | Teacher A   | teacherA.com |
|    284 | Person    | A        | personA.com |       245 |        68 | Teacher B   | teacherB.com |
|    284 | Person    | A        | personA.com |       176 |        41 | Teacher C   | teacherC.com |
+--------+-----------+----------+-------------+-----------+-----------+-------------+--------------+

Basically, I want to get user with distinct Teacher instead of distinct Project. How can I retrieve that with SQL?

The database structure is something like

User
     UserID, FirstName, LastName Email
Users_in_project
     UserID, ProjectID
Project 
     ProjectID, TeacherID
Teacher
     TeacherID, FirstName, LastName, Email

Thanks

Upvotes: 0

Views: 72

Answers (1)

Vecchiasignora
Vecchiasignora

Reputation: 1315

try it to use max() and group by like this

select cast(t_Users.UserID as int) as UserID, t_Users.FirstName,
       t_Users.LastName, t_Users.Email, max(t_Users_In_Project.ProjectID) projectid,
       t_Teachers.AffiliateID,
       t_Teachers.FirstName + ' ' + t_Teachers.LastName as TeacherName,
       t_Teachers.Email as TeacherEmail
  from t_Users
  left join t_Users_In_Project on t_Users_In_Project.UserID = t_Users.UserID
 inner join t_Projects on t_Projects.ProjectID = t_Users_In_Project.ProjectID
  left join t_Teachers on t_Teachers.TeacherID = t_Projects.TeacherID
 where t_Users_In_Project.ProjectID is not null
 group by t_Users.UserID, t_Users.FirstName, t_Users.LastName, t_Users.Email,
          t_Teachers.AffiliateID, t_Teachers.FirstName, t_Teachers.LastName,
          t_Teachers.Email
 order by t_Users.UserID asc

Upvotes: 1

Related Questions