I have fairly complicated query which searches across 4 tables for one or more keywords:
select distinct textures.id from textures
left join `category_texture` on `category_texture`.`texture_id` = `textures`.`id`
left join `categories` on `categories`.`id` = `category_texture`.`category_id`
left join `tag_texture` on `tag_texture`.`texture_id` = `textures`.`id`
left join `tags` on `tags`.`id` = `tag_texture`.`tag_id`
left join `size_texture` on `size_texture`.`texture_id` = `textures`.`id`
left join `sizes` on `sizes`.`id` = `size_texture`.`size_id`
WHERE ( (textures.name LIKE '%artwork%'
OR categories.name LIKE '%artwork%'
OR tags.name LIKE '%artwork%'
OR sizes.name LIKE '%artwork%') AND (textures.name LIKE '%super%'
OR categories.name LIKE '%super%'
OR tags.name LIKE '%super%'
OR sizes.name LIKE '%super%') AND (textures.name LIKE '%master%'
OR categories.name LIKE '%master%'
OR tags.name LIKE '%master%'
OR sizes.name LIKE '%master%') ) AND `textures`.`is_published` = 1 group by `tags`.`name`, `categories`.`name`, `sizes`.`name`, `textures`.`id`
In this example, artwork is a category, master and super are tags. The problem is that if I repeat any of the tables, no results are found:
- artwork (category) + master (tag) works
- artwork works
- artwork + master + super does NOT work - it should print all the textures which have artwork as category and 2 tags (master AND super), but it doesn't.
Thanks in advance.
Editing for clarity:
My goal is to be able to search for data within those tables, in a way that I can combine multiple instances of the same table. For instance, if I search for "artwork super master", it should return all my textures with category "artwork" (because it's the only place where the word is found) and the tags "super" and "master" (both of them).
Currently with this query I'm able to search from within any of those tables, but only if my search doesn't find 2+ things in the same table. So, searching for a category + a texture name + a tag + a size works, but searching for something which is found as 2 tags fails.
See Question&Answers more detail:os