1

There are 3 tables: Students table, Subjects table, and Examinations table.

Students table: 
+------------+--------------+
| student_id | student_name |
+------------+--------------+
|          1 | Alice        | 
|          2 | Bob          | 
|         13 | John         | 
|          6 | Alex         | 
+------------+--------------+

Subjects table: 
+--------------+
| subject_name | 
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+

Examinations table: 
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
|          1 | Math         |
|          1 | Physics      |   
|          1 | Programming  |   
|          2 | Programming  |   
|          1 | Physics      |   
|          1 | Math         |   
|         13 | Math         |   
|         13 | Programming  |   
|         13 | Physics      |   
|          2 | Math         |   
|          1 | Math         |   
+------------+--------------+

SQL code is as below: 

SELECT s.student_id,s.student_name,b.subject_name,COUNT(e.subject_name) as attended_exams
FROM Students as s
INNER JOIN Subjects as b
LEFT JOIN Examinations as e
ON s.student_id=e.student_id AND b.subject_name=e.subject_name
GROUP BY s.student_id,b.subject_name;

The part I am confused with is the INNER JOIN because Students table and Subjects table has no intersection or common columns. I guess my goal is to intuitively understand/imagine a table right before the LEFT JOIN was executed. And also is it necessary to include the Subject table at all since we have all the subjects in the Examinations table already? Really appreciate the help!

This is actually a problem from LeetCode database. Link is below:
https://leetcode.com/problems/students-and-examinations/

GMB
  • 216,147
  • 25
  • 84
  • 135
DLW
  • 121
  • 1
  • 2
  • 8
  • 2
    Your code will return an error in Postgres because you have two `join`s and only one `on` clause. – Gordon Linoff Jan 13 '20 at 22:36
  • @GordonLinoff I selected the wrong tab. Changed to mysql. Thanks for pointing it out. – DLW Jan 13 '20 at 22:44
  • 3
    Most people don't want to pay for access to your problem. You should copy and paste it in your question. Your join is incorrect in any flavor of SQL. I will write an answer now, without knowing your problem and assuming some things from your data structure. – Pan Jan 13 '20 at 22:53
  • 1
    Does this answer your question? [How to use mysql JOIN without ON condition?](https://stackoverflow.com/questions/16470942/how-to-use-mysql-join-without-on-condition) – philipxy Jan 13 '20 at 23:39
  • 1
    [ask] [help] Please research/google before considering asking. Read the manual re syntax/functionality you are using. Also this is a(n obvious) faq. For code questions give a [mre]. Make your posts self-contained. Ask 1 question per post. PS You say you are confused but you are not clear about what. Eg So what if tables have "no intersection or common columns"? Use enough words, sentences & references to parts of examples. Explain your expections and/or ask a question re an authoritative presentation of what you don't get. PS [Re query meanings.](https://stackoverflow.com/a/33952141/3404097) – philipxy Jan 13 '20 at 23:43

4 Answers4

3

You query is equivalent to:

SELECT s.student_id, s.student_name, b.subject_name, 
       COUNT(e.subject_name) as attended_exams
FROM Students s CROSS JOIN
     Subjects b LEFT JOIN
     Examinations as e
     ON s.student_id = e.student_id AND 
        b.subject_name = e.subject_name
GROUP BY s.student_id, b.subject_name;

MySQL extends the JOIN syntax so the ON clause is optional. Personally, I think this is a very bad idea. A CROSS JOIN is a CROSS JOIN and should be specified as such.

What the query is doing is generating a row for every student/subject combination. It is then counting how many examinations each student has attended in each subject.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

In MySql, when using join or inner join the on condition is optional and, where it is not specified, the query is equivalent to a cross join. More detailed solution here

Renato
  • 2,077
  • 1
  • 11
  • 22
2

The JOIN with Subjects table is not needed. You probably also need an IFNULL() for Alex, who unfortunately did not attend any exams.

Try this:

SELECT 
s.student_id, s.student_name, e.subject_name, IFNULL(COUNT(e.subject_name), 0) as attended_exams
FROM Students as s
LEFT JOIN Examinations as e
  ON s.student_id = e.student_id
GROUP BY s.student_id, e.subject_name
;
Pan
  • 331
  • 1
  • 7
  • 1
    All the data in the `Subjects` table is present in the `Examinations` table. There's no need to make an inefficient query involving the `Subjects` table and `CROSS JOIN`. – Pan Jan 14 '20 at 23:06
1

As Renato just pointed out it's a cross join between Students and Subjects. The rest is merely a way to flag if a student toke the exam in that subject or not as the COUNT will not count nulls.

jean
  • 4,159
  • 4
  • 31
  • 52