mysql - WHERE LIKE with multiple keywords

Question

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:

1. artwork (category) + master (tag) works
2. artwork works
3. 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.

Answer 1

I had a rethink and perhaps this approach is better:

SELECT T1.id
FROM 
(  
select textures.id 
from textures
WHERE textures.name LIKE '%texture1%' 
UNION 
select textures.id 
from textures
join category_texture on category_texture.texture_id = textures.id
join categories on categories.id = category_texture.category_id
WHERE categories.name LIKE '%texture1%'
UNION
select textures.id 
from textures
join tag_texture on tag_texture.texture_id = textures.id
join tags on tags.id = tag_texture.tag_id
WHERE tags.name LIKE '%texture1%'
UNION 
select textures.id 
from textures
join size_texture on size_texture.texture_id = textures.id
join sizes on sizes.id = size_texture.size_id 
WHERE sizes.name LIKE '%texture1%'
) AS T1
JOIN 
(  
select textures.id 
from textures
WHERE textures.name LIKE '%category2%' 
UNION 
select textures.id 
from textures
join category_texture on category_texture.texture_id = textures.id
join categories on categories.id = category_texture.category_id
WHERE categories.name LIKE '%category2%'
UNION
select textures.id 
from textures
join tag_texture on tag_texture.texture_id = textures.id
join tags on tags.id = tag_texture.tag_id
WHERE tags.name LIKE '%category2%'
UNION 
select textures.id 
from textures
join size_texture on size_texture.texture_id = textures.id
join sizes on sizes.id = size_texture.size_id 
WHERE sizes.name LIKE '%category2%'
) AS T2
ON T1.id = T2.id
JOIN
(  
select textures.id 
from textures
WHERE textures.name LIKE '%tag3%' 
UNION 
select textures.id 
from textures
join category_texture on category_texture.texture_id = textures.id
join categories on categories.id = category_texture.category_id
WHERE categories.name LIKE '%tag3%'
UNION
select textures.id 
from textures
join tag_texture on tag_texture.texture_id = textures.id
join tags on tags.id = tag_texture.tag_id
WHERE tags.name LIKE '%tag3%'
UNION 
select textures.id 
from textures
join size_texture on size_texture.texture_id = textures.id
join sizes on sizes.id = size_texture.size_id 
WHERE sizes.name LIKE '%tag3%'
) AS T3
ON T1.id = T3.id

Just add more/less JOIN...ON T1.id = Tn.id sections to match your parameters.

Here is a fiddle to show it executing: http://sqlfiddle.com/#!9/9abe6/1