CODEWITHSUNDEEP
phpsql
Cyberomin
Cyberomin

Reputation: 523

Searching multiple tables in query

SELECT  DISTINCT business.name AS businessname
        ,business.description AS description
FROM    business
        , category
        , sub_categories 
WHERE   business.cityID = '$city' 
        AND (category.name LIKE '%$name%' 
             OR sub_categories.name LIKE '%$name%')
        AND business.status = 0

Pls the above SQL code is suppose to search a set of two tables the ones in the bracket and return the result, but for some reason, it's not doing so. What am i doing wrong?

Thank You.

Upvotes: 0

Views: 141

Answers (2)

Martin Schapendonk
Martin Schapendonk

Reputation: 13506

Your query would produce a cartesian product. Depending on the size of your tables that could take a considerable amount of time.

Based on your clarification I'd use a subquery to check for matching categories, this way you don't have to use distinct in your query as it would only return each business once. I also suggest you to start with a decent SQL tutorial.

SELECT  name AS businessname
       ,description AS description
FROM    business
WHERE   cityID = '$city' 
AND     status = 0
AND (   categoryID in (select id from category where name like '%$name%') 
    or  subcategoryID in (select id from sub_categories where name like '%$name%')
)

Upvotes: 2

cdhowie
cdhowie

Reputation: 169028

Two things come to mind:

  1. You are not joining any of the three tables together. Consider adding a few LEFT JOIN clauses.
  2. You are selecting columns from only one table. If you wanted columns from other tables, you should add them to your SELECT clause.

Upvotes: 0

Related Questions