CODEWITHSUNDEEP
mysqlsql
Priyanka Kumara
Priyanka Kumara

Reputation: 25

How Can I Fix This Select Query

This is my query..

SELECT tcs.tutor_id AS tid, tcs.category_id AS cid, tcs.subject_id AS sid, GROUP_CONCAT( DISTINCT s.subjects SEPARATOR ', ') AS subjects,
t.tutor_name, t.tutor_code AS tcode, DATE_FORMAT(t.registration_date, '%b %D, %Y') AS date, t.qualification,
GROUP_CONCAT( DISTINCT o.option_name SEPARATOR ', ') AS tutor_option, timg.image_name AS img
FROM tutor_category_subject as tcs
INNER JOIN subject AS s ON tcs.subject_id = s.subject_id
INNER JOIN tutor_option AS toption ON toption.tutor_id = tcs.tutor_id
INNER JOIN options AS o ON toption.option_id = o.option_id
INNER JOIN tutors AS t ON tcs.tutor_id = t.tutor_id
LEFT JOIN tutor_images AS timg ON timg.tutor_id = tcs.tutor_id
WHERE s.subjects LIKE '%business%' AND timg.image_type = 'profile'
GROUP BY tcs.tutor_id;

This query working properly.. But there is problem with timg's image_type coloum. It is tutor may have their profile image and some tutors dont have profile image. But I need to select all tutors with this condition even if not their profile images. In this query, if there is no profile image to the tutor that tutor not selecting... so how can I do this..?

any comments are greatly appreciated. Thank you

Upvotes: 0

Views: 77

Answers (2)

bendataclear
bendataclear

Reputation: 3850

You could also do it in the WHERE section:

WHERE s.subjects LIKE '%business%' AND (timg.image_type = 'profile' OR timg.image_type is NULL)

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

You just need to move the filter in the WHERE clause to the JOIN clause for tutor_images:

SELECT tcs.tutor_id AS tid, tcs.category_id AS cid, tcs.subject_id AS sid, GROUP_CONCAT( DISTINCT s.subjects SEPARATOR ', ') AS subjects,
t.tutor_name, t.tutor_code AS tcode, DATE_FORMAT(t.registration_date, '%b %D, %Y') AS date, t.qualification,
GROUP_CONCAT( DISTINCT o.option_name SEPARATOR ', ') AS tutor_option, timg.image_name AS img
FROM tutor_category_subject as tcs
INNER JOIN subject AS s ON tcs.subject_id = s.subject_id
INNER JOIN tutor_option AS toption ON toption.tutor_id = tcs.tutor_id
INNER JOIN options AS o ON toption.option_id = o.option_id
INNER JOIN tutors AS t ON tcs.tutor_id = t.tutor_id
LEFT JOIN tutor_images AS timg ON timg.tutor_id = tcs.tutor_id AND timg.image_type = 'profile'
WHERE s.subjects LIKE '%business%' 
GROUP BY tcs.tutor_id;

Upvotes: 3

Related Questions