CODEWITHSUNDEEP
mysqlsqlsequelize.js
Ask the cat
Ask the cat

Reputation: 47

How to perform inner join for this query

Can someone help me trouble shoot my syntax i spent 1 hour on it yet i cant figure it out.

I have 3 tables:

**Student**

-ID (UUID)

email (String)

**Teacher**

-ID (UUID)

email (String)

**StudentTeacher**

-ID (UUID)

studentId (UUID)

teacherId (UUID)

I have an initiate query which returns some studentId

SELECT studentId FROM TeacherStudents WHERE teacherId IN ('123', '456') GROUP BY studentId HAVING COUNT(DISTINCT teacherId) = 2

I would like to join the results with Students table so i can return the email address

However, this is not working

SELECT email FROM TeacherStudents WHERE teacherId IN ('123', '456') GROUP BY studentId HAVING COUNT(DISTINCT teacherId) = 2 INNER JOIN Students on TeacherStudents.studentId = Students.id

It returns an error.

Upvotes: 0

Views: 34

Answers (2)

MatBailie
MatBailie

Reputation: 86716

The generalised case, to get all columns from the Student table...

SELECT
  Students.*
FROM
(
  SELECT studentId
    FROM TeacherStudents
   WHERE teacherId IN ('123', '456')
GROUP BY studentId
  HAVING COUNT(DISTINCT teacherId) = 2
)
  StudentsOfInterest
INNER JOIN
  Students
    ON StudentsOfInterest.studentId = Students.id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

The correct syntax is:

SELECT s.studentId, s.email
FROM TeacherStudents ts JOIN
     Students s
     ON ts.studentId = s.id
WHERE ts.teacherId IN (123, 456)
GROUP BY s.studentId, s.email
HAVING COUNT(DISTINCT ts.teacherId) = 2 ;

Notes:

  • WHERE is a SQL clause that follows the FROM clause.
  • JOINs are an operator in the FROM clause.
  • TeacherId is probably a number, so do not use single quotes.
  • Table aliases make the query easier to write and to read.
  • You are learning SQL, so be sure the unaggregated columns in SELECT match the GROUP BY. You can drop s.studentId from the SELECT if you really want to.
  • Qualify all column names so you know what table they come from.

Upvotes: 1

Related Questions