How Can I Fix This Select Query....

[thara]

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

Edited October 31, 2012 by thara

[krisw44]

It looks like you just need to remove

 

AND timg.image_type = 'profile'

 

from the second to last line. Then it should select all of the images, no matter the type.

[thara]

but... if there are some profile images to the tutors I want to select those images too...

[Muddy_Funster]

You could use a CASE in the WHERE :

WHERE ((s.subjects LIKE '%business%') AND (CASE WHEN SELECT timg.image_type = 'profile' IS NOT NULL THEN timg.image_type = 'profile' ELSE timg.image_type = 'something else'))

 

 

This isn't that close to being actualy correct, but if you look up the MySQL manual on CASE then it should be enough for you to be able to fix it.

[DavidAM]

Move the AND condition into the LEFT JOIN

 

LEFT JOIN tutor_images AS timg ON timg.tutor_id = tcs.tutor_id AND timg.image_type = 'profile'
WHERE s.subjects LIKE '%business%'

[Muddy_Funster]

Move the AND condition into the LEFT JOIN

 

LEFT JOIN tutor_images AS timg ON timg.tutor_id = tcs.tutor_id AND timg.image_type = 'profile'
WHERE s.subjects LIKE '%business%'

I can't see how that helps select the profile image if it is there, and a different image if it's not.....could you explain the logic here?

[nodirtyrockstar]

This is a PHP forum. You will probably have better luck in the MYSQL thread.

[Barand]

This is a PHP forum. You will probably have better luck in the MYSQL thread.

 

Why would you say that when DavidAM has already provided the correct solution?

 

Don't you read the replies?

[Barand]

 

I can't see how that helps select the profile image if it is there, and a different image if it's not.....could you explain the logic here?

 

When you use LEFT JOIN there are not always values present from the left-joined table (or you may as well have INNER JOIN). For that reason it's useless having a condition in the WHERE clause that refers to that table - it needs to go in the JOIN condition so that only those records are matched.

[Muddy_Funster]

@ Barand -

I get the difference between joins, I'm just not sure where the second part of "If there is a profile image, use it. If not, use another image instead" comes into it. Which is the question as I understand it (but then again my understanding of things is seldom accurate)

[DavidAM]

... But I need to select all tutors with this condition even if not their profile images. ...

 

I did not see anything in the OP's posts about selecting a different image if the profile image is not present. If there are other images that need to be chosen when the "profile" image does not exist, we would need to know the structure and selection criteria in order to make a suggestion.