Searching two tables

Question

I currently have following two tables:

CREATE TABLE files_list
(
    'listid' INT, 
    'name' VARCHAR(25), 
    'synonym' VARCHAR(25), 
    'description' VARCHAR(25)
);

CREATE TABLE files_tags
(
    'tag_name' VARCHAR(25), 
    'listid' INT
);

If someone uses the keyword "dragon ball", at the moment, I use following query to search my_list for possible matches:

SELECT * 
FROM files_list
WHERE name LIKE '%dragon%' 
OR synonym LIKE '%dragon%' 
OR description LIKE '%dragon%' 
OR name LIKE '%ball%' 
OR synonym LIKE '%ball%' 
OR description LIKE '%ball%'

I am not sure how to search both tables using one query. I want to show the user following data in the search result: name, synonym, description and all tags.

My Questions 1. Is there any way to make the current mysql query shorter? 2. How can I combine it with files_tags table, - to show rows from files_list which has a match in files_tags, but not in files_list? - to show rows from files_list which has a match in files_list, but may not infiles_tags`?

You can see the current displayed result at http://hsbsitez.com/

http://stackoverflow.com/questions/394041/mysql-how-to-search-multiple-tables-for-a-string-existing-in-any-column See this question your answer is there :) — fmsf, Jan 24 '09 at 22:31
Possible duplicate of [MySQL: How to search multiple tables for a string existing in any column](http://stackoverflow.com/questions/394041/mysql-how-to-search-multiple-tables-for-a-string-existing-in-any-column) — KindaTechy, Feb 20 '17 at 21:57

Sijmen · Answer 1 · 2016-09-16T13:44:04.363

It would make more sense to join the tables.

SELECT name, synonym, description, tag_name FROM `files_list` WHERE ( name LIKE '%dragon%' OR synonym LIKE '%dragon%' OR description LIKE '%dragon%' OR name LIKE '%ball%' OR synonym LIKE '%ball%' OR description LIKE '%ball%' )
OUTER JOIN files_tags on files_tags.listid = files_list.listid

OUTER JOIN will make sure all tags are selected, this will create duplicate rows when multiple tags per id are available, with the only difference being the tag

score 0 · Answer 2 · answered Jan 26 '09 at 04:41

Be careful when using union to actually get the columns you want for each unioned query. And this case, a subquery seems to make more sense:

SELECT name, synonym, description FROM files_list WHERE
       name LIKE '%dragon%' OR synonym LIKE '%dragon%' OR description LIKE '%dragon%'
       OR name LIKE '%ball%' OR synonym LIKE '%ball%' OR description LIKE '%ball%'
       OR listid IN (SELECT listid FROM files_tags WHERE tag_name='dragon' OR tag_name='ball');

Some additional questions: When you say you want show the user "all tags", do you mean all tags that exist for a given listid? Or just those that matched? And if description like "%dragon%", is dragon one of the tags you want returned even if it isn't in file_tags?

Searching two tables

2 Answers2