Creating effective query with multiple tables

[thara]

I am trying to make a query for site searching. What I need to do with this query is I want to select four main columns according to search keyword (tutors, institutes, subjects, cities). when typing keyword the query must check this four tables and need to display search result under this four categories. when displaying search result I need to add some values to make meaningful search result. Eg: Think displaying tutors under tutors category as a search result I need to display his/her name, city, profile image etc.

 

Its something like this.

 

Tutors

tutor's name

city

profile image

institutes

institute's name

city

profile image

subjects

subject name

category name which belong this subject

cities

city name

district name which city belong etc...

 

 

 

I tried it something like this

 

 

SELECT keyword, col, city_name, image_name, tutor_code FROM (
      SELECT tutor_name AS keyword, 'Tutors' AS col, IFNULL(c1.city_name, '') city_name, IFNULL(ti.image_name, '') image_name, tutor_code FROM tutors AS t
      LEFT JOIN address a ON t.address_id = a.address_id
      LEFT JOIN city c1 ON a.city_id = c1.city_id
      LEFT JOIN tutor_images ti ON t.tutor_id = ti.tutor_id AND ti.image_type = 'profile'
      UNION
      SELECT subject_name AS keyword, 'Subject' AS col, '' city_name, '' image_name, '' tutor_code FROM subjects
      UNION
      SELECT city_name AS keyword, 'City' AS col, '' city_name, '' image_name, '' tutor_code FROM city
      UNION
      SELECT institute_name AS keyword, 'Institute' AS col, '' city_name, '' image_name, '' tutor_code FROM institutes AS i
      LEFT JOIN address a ON i.address_id = a.address_id
      LEFT JOIN city c2 ON a.city_id = c2.city_id
      LEFT JOIN institute_images ii ON i.institute_id = ii.institute_id AND ii.image_type = 'profile'
) s
WHERE keyword LIKE '%$queryString%'
LIMIT 10

 

this query work about 50% but cant get to work in 100%. Problem is it is not display city name and profile image under institutes category, cant display district name and category names under cities and subjects fields. And also I would like to this kind of query is ineffective and does it waste time and resources?

 

Thank you.

Edited February 10, 2013 by Zane

[thara]

Any comments are highly appreciating?

Thank you.

[Tychonaut]

It's kind of confusing what you are asking. What would be a keyword that someone would search for? If I search for "math" .. how would cities show up for that? Or am I searching for cities .. "London" .. and then the tutors in London are showing?

 

It seems like you have 4 categories that would not have results for a single keyword. What one word would give me relevant tutors, institutes, subjects, and cities?

 

I think the whole logic could be re-thought to make your problem easier.