Reputation: 1448
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 in
files_tags`?
You can see the current displayed result at http://hsbsitez.com/
Upvotes: 6
Views: 1112
Reputation: 69
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
Upvotes: 1
Reputation: 98498
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?
Upvotes: 0