SQL many to many

I have fivetables here:

User

+--------+----------+
| stu_id | batch-id |
+--------+----------+
|      1 |        1 |
|      2 |        1 |
|      3 |        1 |
+--------+----------+

subject_id

+------------+--------------+
| subject_id | subject_name |
+------------+--------------+
|          1 | mathematics  |
|          2 | science      |
|          3 | english      |
+------------+--------------+

batch

+----------+------------+
| batch_id | batch_name |
+----------+------------+
|        1 |   20182019 |
|        2 |   20202021 |
|          |            |
+----------+------------+

subject_batch

+----------+------------+
| batch_id | subject_id |
+----------+------------+
|        1 |          1 |
|        1 |          2 |
|        1 |          3 |
|          |            |
+----------+------------+

subject_assessment


+--------+----------+
| stu_id | batch_id |
+--------+----------+
|      1 |        1 |
+--------+----------+

The SELECT that i am trying to achieve is

+--------+----------+------------+--------------+
| stu_id | batch_id | subject_id | subject_name |
+--------+----------+------------+--------------+
|      1 |        1 |          1 | mathematics  |
|      1 |        1 |          2 | science      |
|      1 |        1 |          3 | english      |
|        |          |            |              |
+--------+----------+------------+--------------+

I tried =

$query = "SELECT subject.subject_name, user.first_name, batch.batch_name 
from subject_assessment inner join user 
ON subject_assessment.student_id = user.user_id inner 
join batch ON subject_assessment.batch_id = batch.batch_id  
left join subject_batch on = subject.subject_name = subject_batch.subject_id";

but fail. It works when i remove

subject.subject_name

and

left join left join subject_batch on = subject.subject_name = subject_batch.subject_id"

But then i wouldnt be able to retrieve subject_id and subject_name.

Appreciate if you could help.Thank you in advance.

Answer

Here is how it should look like:

select user.stu_id, user.batch_id, subject.subject_id, subject.subject_name
from user 
inner join subject_batch on user.batch_id = subject_batch.batch_id 
inner join subject on subject.subject_id = subject_batch.subject_id
order by user.stu_id;

Here is where you can run it:

https://paiza.io/projects/RtN8fMS1vVlQ2mtEYpqf7g?language=mysql

Advertisement

Answer