mysql - select values from tables LIKE values from another table
Solution 1
Try this :
SELECT
u.id, u.user_name, i.item_name, i.color
FROM
users AS u,
items AS i
WHERE
i.id = u.id
AND
i.item_name LIKE "%shoes%"
AND
i.color LIKE "%blue%"
For the second part, if you look at this page on MySQL dev site you will see that you can use REGEXP
to match your result instead of LIKE
so you I think you can use something like
REGEXP 'john|steven|bob'
Solution 2
You need to have an inner join with items. Also I don't think you can use desc. Have used descr in my answer instead
Haven't tested this, but please try the following
SELECT u.id, u.user_name, i.item_name as descr
FROM users u INNER JOIN items i
ON i.id = u.id
WHERE i.item_name LIKE '%shoes%'
AND i.color LIKE '%blue%'
Solution 3
After hours of hopless searching correct syntax i found what i was looking for. Best way, instead of using regexp and complicating a query i suggest using concat all column names into on string and then search it via as many LIKEs as we have keywords. Maybe that doesnt look elegant but is good enough for me. Example:
SELECT user_name, item_name FROM table WHERE CONCAT(user_name, item_name) LIKE '%keyword1%'
Look out for NULL records - if you concat only one field with null, the result becomes null too so use IFNULL(null_record, '').
Hope it helps anyone.
Solution 4
SELECT *
FROM users u
WHERE
u.id IN (SELECT id
FROM items
WHERE
items.id=u.id
AND
color LIKE '%$search%'
AND
item_name LIKE '%$search%'
)
Kalreg
Updated on March 13, 2020Comments
-
Kalreg about 4 years
I have MySQL database with two tables: users and items. They look like this:
users:
id | user_name | desc -------------------------- 1 | john | tall 2 | dave | fat 3 | maria | pretty
items:
id | item_name | color -------------------------- 1 | trousers | red 2 | shoes | blue 3 | shoes | red
I want to select from database list of items that have specific id and name of it is LIKE a keyword. It's for use of simple search engine. I am making such query:
SELECT id, user_name, ( SELECT item_name FROM items WHERE id = u.id ) as desc FROM users u WHERE desc LIKE "%shoes%" AND color LIKE "%blue%"
As a result, I would expect one line containing id = 2, username = dave and itemname = shoes because it's the only one row fulfilling my query. Unfortunately, I get a message that there is no a 'desc' column. I know that there is not such a column in 'users' but how tell MySQL to grab it from subquery named desc?
Additional question: is that possible to work with WHERE ... LIKE command and array style like IN (val1, val2, val3)? I mean instead of loooong queries:
SELECT name FROM users WHERE name LIKE "%john%" OR name LIKE "%steven%" OR name LIKE "bob%"
make shorter:
SELECT name FROM users WHERE name LIKE IN ( "%john%", "%steven%", "%bob%")
Thanks in advance.
-
Kalreg about 12 yearsthank you - your advice works good for me. havent read article you proposed but i will. +1 for you
-
j0k over 11 yearsInstead of doing crazy HTML inside your answer, you should better only use 4 spaces to indend your code. The coloration & highlight will be automatically apply.