CODEWITHSUNDEEP
mysqlsqlselectunion
ugnuku
ugnuku

Reputation: 161

making query with multiple tables

SELECT keyword 
FROM
(
    SELECT tutor_Name AS keyword FROM t
    UNION
    SELECT subject_name AS keyword FROM s
    UNION
    SELECT institute_name AS keyword FROM i
) s
WHERE keyword LIKE '%$queryString%' 
LIMIT 10

this query display a list with subjects, tutor names, cities according the keyword. Now I need to modify this query with more values. For an example there is a tutor name in the list I want to display his/her current city, his/her profile image etc. So I need to get those values from my city table, address table, tutorImages table.. So anybody can help me to make this query?

Upvotes: 1

Views: 156

Answers (4)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this it will work: 

$q = "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 subjects AS keyword, 'Subject' AS col, '' city_name, '' image_name, '' tutor_code FROM subject
                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
            ) s
          WHERE keyword LIKE '%$queryString%' 
          LIMIT 10";

Upvotes: 2

Michel Feldheim
Michel Feldheim

Reputation: 18250

The problem with your first query is, that you fetch all results of each table unfiltered, union them, distinct them, then filter the result. This is quite ineffective in terms of query time and resources.

What you need here are JOINs. To understand how joins work, read this

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

and this

http://dev.mysql.com/doc/refman/5.5/en/join.html

To give you an idea of how your query should look like: the old one refactored

SELECT

give me a moment, switching from mobile to pc.. Read the article while ;)

Sorry, took a bit longer. I see you have found a solution meanwhile so I will not bother digging into this longer

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT keyword, city_name, image_name
FROM (
SELECT tname AS keyword, IFNULL(c1.city_name, '') city_name, IFNULL(ti.image_name, '') image_name FROM 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 tutorImages ti ON t.tutor_id = t1.tutor_id 
UNION
SELECT sname AS keyword, '' city_name, '' image_name FROM sub
UNION
SELECT cname AS keyword, '' city_name, '' image_name FROM c
UNION
SELECT iname AS keyword, '' city_name, '' image_name FROM i ) s
WHERE keyword LIKE '%$queryString%' 
LIMIT 10

Upvotes: 1

JPR
JPR

Reputation: 869

Try a join.

SELECT table1.field, table2.field, table3.field 
FROM table1 
JOIN table2 ON table1columnToMatch = table2columnToMatch
JOIN table3 ON table2columnToMatch = table3columnToMatch
WHERE tableWithKeyword.keyword = $keyword

Upvotes: 0

Related Questions