mysql - select values from tables LIKE values from another table

Question

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.

Jérémie Parker · Accepted Answer · 2012-05-01 16:51:39Z

4

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'

edited May 1, 2012 at 16:51

answered May 1, 2012 at 16:39

Jérémie Parker

3,1742 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kalreg Over a year ago

thank you - your advice works good for me. havent read article you proposed but i will. +1 for you

Chetter Hummin · Accepted Answer · 2012-05-01 16:37:52Z

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%'

answered May 1, 2012 at 16:37

Chetter Hummin

6,8478 gold badges34 silver badges45 bronze badges

Comments

Kalreg · Accepted Answer · 2012-05-02 18:36:04Z

1

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.

answered May 2, 2012 at 18:36

Kalreg

9413 gold badges12 silver badges31 bronze badges

Comments

Stephan · Accepted Answer · 2012-11-26 10:37:12Z

1

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%'
            )

edited Nov 26, 2012 at 10:37

Stephan

43.2k69 gold badges245 silver badges342 bronze badges

answered Nov 26, 2012 at 10:16

Jathin

111 bronze badge

1 Comment

j0k Over a year ago

Instead 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.

Collectives™ on Stack Overflow

mysql - select values from tables LIKE values from another table

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related