[RESOLVED] Problem In My Select Query Again..

[thara]

hello.. again to my select query... this is my updated one.

  $q = "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 AS tname,
        t.tutor_code AS tcode,
        DATE_FORMAT(t.registration_date, '%b %D, %Y') AS date,
        t.qualification AS qualifi,
        GROUP_CONCAT( DISTINCT o.option_name SEPARATOR ', ') AS tutor_option,
        timg.image_name AS img,
        city_name AS city,
        d.district_name AS district
   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
        INNER JOIN address ON address.address_id = t.address_id
        INNER JOIN city ON city.city_id = address.city_id
        INNER JOIN district AS d ON d.district_id = city.district_id
        LEFT JOIN tutor_images AS timg ON timg.tutor_id = tcs.tutor_id AND timg.image_type = 'profile'
   WHERE s.subjects LIKE '%$subject%'
   GROUP BY tcs.tutor_id";

this query is working now.. now I found a problem. it is..... so many subject may have to one particular tutor. I need to select all the subject that pertaining to a tutor with searching keyword to display with search result. In this query searching keyword is '$subject'.
This query only selecting subjects to the tutor similar to the given keyword. For an example if a user give a keyword like 'business' query selecting subjects to that tutor like 'business studies, business accounting., etc only'. so I need to select other subjects too through this query.. can anybody help me?

thank you. [RIGHT][/RIGHT]

[Ashley_Sheridan]

I'm not really sure what you're asking for. Without having seen your database layout, this looks like it would work to select the corresponding information where a subject contains the keyword $subject. Did you want to allow searching for more than one subject?

[thara]

yes.. one tutor may have so many subjects.. those all subjects need to select with this searching query.. clear????

[Ashley_Sheridan]

Ah, I see, you want to see all their subjects, but only if at least one of their subjects contains the keyword.

I guess you could use a sub-query to get all the tutor_id values for tutors with a matching subject, and then use that set as the where clause in the outermost query, if that makes sense?

[thara]

thanks.. can you explain it with an example???

[thara]

yes.. one tutor may have so many subjects.. those all subjects need to select with this searching query.. clear????

[Ashley_Sheridan]

A basic example would be something like:

SELECT *
FROM table_a a
WHERE a.field IN (SELECT b.id FROM table_b b WHERE b.field LIKE '%keyword%')

[thara]

ok.. thanks for your help.. I greatly appreciated it..