CODEWITHSUNDEEP
mysqlmysql-workbench
user5050407
user5050407

Reputation: 85

how can i merge multiple joins in mysql (sample query given)

I need to have below join repeated for multiple students. How can optimize and write it as a single query?

select * from marks mar
join subject subj ON mar.subject_id=subj.subject_id
join student std ON subj.student_id = std.sutdent_id
Where std.student_name="xyz" and std.location="abc" and age="18"

select * from marks mar
join subject subj ON mar.subject_id=subj.subject_id
join student std ON subj.student_id = std.sutdent_id
Where std.student_name="ccc" and std.location="kkk" and age="19"

Upvotes: 1

Views: 26

Answers (2)

Quassnoi
Quassnoi

Reputation: 425331

This will parse in MySQL:

SELECT  *
FROM    marks mar
JOIN    subject subj
ON      subj.subject_id = mar.subject_id
JOIN    student std
ON      std.student_id = subj.student_id
WHERE   (std.student_name, std.location, age) IN (('xyz', 'abc', 18), ('ccc', 'kkk', 19))

but not give a better execution plan.

Alternatively, use this:

SELECT  *
FROM    marks mar
JOIN    subject subj
ON      subj.subject_id = mar.subject_id
JOIN    student std
ON      std.student_id = subj.student_id
WHERE   (std.student_name, std.location, age) = ('xyz', 'abc', 18)
        OR
        (std.student_name, std.location, age) = ('ccc', 'kkk', 19)

Upvotes: 1

LDMJoe
LDMJoe

Reputation: 1589

Is ...

select * from marks mar
join subject subj ON mar.subject_id=subj.subject_id
join student std ON subj.student_id = std.sutdent_id
Where 
    (std.student_name="xyz" and std.location="abc" and age="18")
    OR
    (std.student_name="ccc" and std.location="kkk" and age="19")

...oversimplifying it?

Upvotes: 1

Related Questions