Mysql query with multiple tables?

Question

I hope you can help. I want to do a keyword search on products including multiple tables. But not every product has any entries in each table...

Example

table 'product'
id     product_name     description
1      my product       this is my favorite product
2      another item     that is another product
3      blue car         for driving away

table 'keywords'
prod_id    keywords
2          car, product, blue

So, now I want to get all products, having 'car' in its 'product_name', 'description' or 'keywords'

Just using a simple mysql query will only have those products as result, which are in both tables:

SELECT A.id FROM product A, keywords B WHERE A.ID = B.product_id AND....

I tried LEFT JOIN - but that had all three products as result (maybe I did it wrong):

SELECT A.id FROM product A LEFT JOIN keywords B ON A.id = B.prod_id AND (A.product_name LIKE '%car%' OR A.description LIKE '%car%' OR B.keywords LIKE '%car%')

Can anyone help? Thanks & regards Jan

Hossam Aldeen Ahmed · Accepted Answer · 2014-09-15 09:00:37Z

2

the best way is to use union

    select id from product where product_name LIKE '%car%' OR description LIKE '%car%'
    union 
    select prod_id as id from keywords where keywords LIKE '%car%' ;

answered Sep 15, 2014 at 9:00

Hossam Aldeen Ahmed

7821 gold badge8 silver badges20 bronze badges

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

Comments

Xenos · Accepted Answer · 2014-09-15 09:02:23Z

1

You put the AND in the LEFT JOIN instruction instead of WHERE. Try

SELECT `A.id` FROM product AS `A`
LEFT OUTER JOIN keywords AS `B` ON `A.id` = `B.prod_id`
WHERE (`A.product_name` LIKE '%car%' OR `A.description` LIKE '%car%' OR `B.keywords` LIKE '%car%')

Beware that LIKE '%car%' will also select That's my favorite cardigan.

RLIKE '[[:<:]]word[[:>:]]' may be better.

Resources:

Mysql RegExp for RLIKE: http://dev.mysql.com/doc/refman/5.0/fr/regexp.html

answered Sep 15, 2014 at 9:02

Xenos

3,5172 gold badges31 silver badges52 bronze badges

Comments

RafaelCaballero · Accepted Answer · 2014-09-15 08:56:05Z

1

This can be done using an union:

(select id from product where product_name LIKE '%car%' OR description LIKE '%car%')
union 
(select prod_id from keywords where keywords LIKE '%car%' );

answered Sep 15, 2014 at 8:56

RafaelCaballero

1,6132 gold badges18 silver badges26 bronze badges

Collectives™ on Stack Overflow

Mysql query with multiple tables?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related