I have 3 tables named student, student_lessons and student_image. I want to select student, student lessons and display students image IF EXISTS. Is there anyway to handle it in one query ?
try to select student id, student name, student lesson (must exists) and student image path, student image pic_id if exists
# TABLE `students`
id | name | surname
1 : John : Malkovich
2 : John : Smith
# TABLE `students_lessons` ( no AI id here)
student_id | lesson | note
1 : math : A
1 : geo : B
2 : math : C
# TABLE `students_image`
pic_id | student_id | image_path
1 : 1 : some path
2 : 1 : some path
3 : 2 : some path
if I try to do query with joint, it return zero if it doesn't find any picture. How can I do query to select image if exists in single query ?
SELECT students.*
FROM students
JOIN students_lessons ON (students_lessons.student_id = students.id)
LEFT JOIN students_image ON (students_image.student_id = students.id)
Or if you only care if image exists:
SELECT students.*
FROM students
JOIN students_lessons ON (students_lessons.student_id = students.id)
WHERE EXISTS (
SELECT si.*
FROM students_image si
WHERE si.student_id = students.id
)
I think what you're looking for is a LEFT JOIN. Just modify the SELECT to your linking. This query selects all students and have lessons, wether they have an image or not.