MySQL querying relational tables

I suppose this is more or less trivial thing but here it goes...

I have few relational tables and I'm trying to retrieve rows depending on 2 values. Here's my query so far:

SELECT `inventory`.*, `tags`.*, `tags_inv`.*, `categories`.*, `categories_inv`.*
FROM (`tags`)
LEFT JOIN `tags_inv` ON `tags_inv`.`tag_id` = `tags`.`tag_id`
LEFT JOIN `inventory` ON `inventory`.`inventory_id` = `tags_inv`.`inv_id`
LEFT JOIN `categories_inv` ON `categories_inv`.`inv_id` = `inventory`.`inventory_id`
LEFT JOIN `categories` ON `categories`.`cat_id` = `categories_inv`.`cat_id`
WHERE `categories`.`cat_id` = 1
AND  `inventory`.`inv_name`  LIKE '%p%'
OR  `inventory`.`inv_dim_w`  LIKE '%p%'
OR  `inventory`.`inv_dim_l`  LIKE '%p%'
OR  `inventory`.`inv_dim_h`  LIKE '%p%'
OR  `tags`.`tag_description`  LIKE '%p%'
ORDER BY `inventory`.`inv_name`

开发者_开发问答

The problem is that my query selects everything from my db (like it is ignoring cat_id), but I would like to select only rows with cat_id 1 in this case...

Is there a quick and reasonable way to resolve this? :)

Thanks in advance!

---

Use parenthesis in your WHERE clause to group all of the OR conditions together. Because you have the AND and OR on the same level in the WHERE, it's ignoring the cat_id = 1 filter for all the OR branches.

---

• Remind yourself what a LEFT JOIN b means. It means, we want all rows from table a whether there are matching rows in table b or not. You are using LEFT JOIN for all your joins, but your conditions in the WHERE clause imply that you really want INNER JOIN.

• Review precedence of boolean operators. AND binds more tightly than OR (this is true in all programming languages, not just SQL), so your WHERE clause does the equivalent of:

  WHERE (`categories`.`cat_id` = 1 AND `inventory`.`inv_name`  LIKE '%p%')
    OR  `inventory`.`inv_dim_w`  LIKE '%p%'
    OR  `inventory`.`inv_dim_l`  LIKE '%p%'
    OR  `inventory`.`inv_dim_h`  LIKE '%p%'
    OR  `tags`.`tag_description`  LIKE '%p%'
  

  You need to use parentheses to control the natural operator precedence.

  WHERE `categories`.`cat_id` = 1 AND 
    (`inventory`.`inv_name`  LIKE '%p%'
    OR  `inventory`.`inv_dim_w`  LIKE '%p%'
    OR  `inventory`.`inv_dim_l`  LIKE '%p%'
    OR  `inventory`.`inv_dim_h`  LIKE '%p%'
    OR  `tags`.`tag_description`  LIKE '%p%')
  

• Using the LIKE pattern-matching predicate with leading wildcards means your query cannot use an index to improve efficiency. So it has to read every row of your inventory and tags tables to evaluate the pattern match. To fix this, you can use a fulltext search tool, or else add another column to the tables that contains just the 'p' you are searching for.

  WHERE `categories`.`cat_id` = 1 
    AND (`inventory`.`has_p` = 1 OR `tags`.`has_p` = 1)
  

• It's hard to optimize OR expressions with indexes, because MySQL has a limit in most cases that it can employ only one index per table reference. A workaround is to run multiple queries and UNION them together. That way each reference to the table gets to use its own index.

  SELECT ... WHERE `categories`.`cat_id` = 1 AND `inventory`.`has_p` = 1
  UNION ALL
  SELECT ... WHERE `categories`.`cat_id` = 1 AND `tags`.`has_p` = 1