vibhav krishna vibhav krishna - 27 days ago 16
MySQL Question

MySQL joins returns more then expected rows

i want some records from more then three MySQL Tables with joins but i am getting unexpected records what is wrong with this query..

1)

admit_card TABLE



class_id|exam_id|subject_id| exam_date |exam_time_from|exam_time_to
1 1 S1 02/12/2016 11:00 02:00
2 1 S1 01/12/2016 11:00 02:00
2 1 S4 02/12/2016 11:00 02:00


subjects TABLE



section_id_fk|subject_code|subject_name
1 S1 HINDI
2 S1 HINDI
2 S4 COMPUTER


class_details TABLE



class_id_pk|class_name
1 FIRST
2 SECOND


section_details TABLE



section_id_pk|class_id_fk|section_name|
1 1 A
2 2 A


now I want to fetch record from admit_card table which has three records but here joins are mandatory because in admit card,I have class_id exam_id and subject_id column which can get through their superior table. now my query is like :

SELECT class_name,section_name,subject_code,subject_name,ac.exam_date, ac.exam_time_from,ac.exam_time_to
FROM admit_card ac
LEFT OUTER JOIN section_details sd ON sd.section_id_pk = ac.class_id
JOIN class_details cd ON cd.class_id_pk = sd.class_id_fk
JOIN subjects ON sd.section_id_pk = subjects.section_id_fk
JOIN admit_card ON admit_card.subject_id = subjects.subject_code;


EXPECTED ANSWER



1) FIRST |A| S1| HINDI |02/12/2016 | 11:00| 02:00
2) SECOND |A| S1| HINDI |01/12/2016 | 11:00| 02:00
3) SECOND |A| S4| COMP |02/12/2016 | 11:00| 12:00

Answer

You shouldn't have admit_card in the query twice. To join the columns with different tables, specify them both in the same ON clause.

SELECT class_name,section_name,subject_code,subject_name,ac.exam_date, ac.exam_time_from,ac.exam_time_to 
FROM admit_card ac 
LEFT OUTER JOIN section_details sd ON sd.section_id_pk = ac.class_id 
JOIN class_details cd ON cd.class_id_pk = sd.class_id_fk 
JOIN subjects ON sd.section_id_pk = subjects.section_id_fk AND ac.subject_id = subjects.subject_code

Result:

+------------+--------------+--------------+--------------+---------------------+----------------+--------------+
| class_name | section_name | subject_code | subject_name | exam_date           | exam_time_from | exam_time_to |
+------------+--------------+--------------+--------------+---------------------+----------------+--------------+
| FIRST      | A            | S1           | HINDI        | 2016-02-12 00:00:00 | 11:00          | 02:00        |
| SECOND     | A            | S1           | HINDI        | 2016-01-12 00:00:00 | 11:00          | 02:00        |
| SECOND     | A            | S4           | COMPUTER     | 2016-02-12 00:00:00 | 11:00          | 02:00        |
+------------+--------------+--------------+--------------+---------------------+----------------+--------------+