CODEWITHSUNDEEP
mysqlsqlselectmariadbdbeaver
single_thread
single_thread

Reputation: 37

SQL, select from two tables based on a third, which references them

Just starting with SQL and the first all-nighter is already here.

I have three tables:

student (student_id, first_name, last_name)
course (course_id, name)
exam (ref_to_student_id, ref_to_course_id, score)

How do I make a select statement and list out student name, last name and course name, for all cases where achieved exam score was > X ?

This is my best shot:

SELECT last_name FROM student WHERE student_id IN 
 (SELECT ref_to_student_id FROM exam WHERE score > 50)
UNION ALL
SELECT name FROM course WHERE course_id IN
 (SELECT ref_to_course_id FROM exam WHERE score > 50)

It's faulty, because I get:

last_name
last_name
name
name
name

and there's no way of telling exactly which student scored above X on which exam/course.

In my mind, something like this:

SELECT first_name, last_name, name
FROM student, course
WHERE student_id, course_id
IN (SELECT ref_to_student_id, ref_to_course_id FROM exam WHERE score > 50)

would produce something like this:

John Doe Chemistry
Jane Dove English
...

But, it only produces a notification about an error in the syntax.

Upvotes: 1

Views: 2406

Answers (3)

YouneL
YouneL

Reputation: 8351

You can use inner join between tables like this:

SELECT s.first_name AS first_name, s.last_name AS last_name, c.name AS course_name
FROM student s
INNER JOIN exam e ON e.ref_to_student_id = s.student_id
INNER JOIN course ON c.course_id = e.ref_to_course_id
WHERE e.score > 50;

Upvotes: 2

Jhonatan Serafim
Jhonatan Serafim

Reputation: 58

Friend, try this:

SELECT s.first_name, s.last_name, c.name
FROM exam e
JOIN student s ON s.student_id = e.ref_to_student_id
JOIN course c ON c.course_id = e.ref_to_course_id
WHERE e.score > 50

I hope to help you!

Upvotes: 1

Mureinik
Mureinik

Reputation: 311228

You need to join these tables, not union them:

SELECT last_name, first_name, name, score
FROM   student s
JOIN   exam e ON s.student_id = e.ref_to_student_id
JOIN   course c ON e.red_to_course_id = c.course_id
WHERE  score > 50

Upvotes: 1

Related Questions