1

Say I've two tables products and brands having below data.

tbl_products            
ID  Name               BrandID    Price
1   Keyboard             1        100
2   Keyboard             2        120
3   Keyboard wireless    1        130
4   Keyboard wireless    2        150

tbl_brands  
ID  Name
1   Microsoft
2   Dell
3   HP

What I want is when I type 'Microsoft Keyboard' or 'Keyboard Microsoft' then it should list me product ID 1 and 3 not 2 or 4 even 2 or 4 has keyboard. I may search for more keywords but it should give me only the items matching itself. 

SELECT p.*, b.Name BrandName FROM tbl_products p INNER JOIN tbl_brands b ON b.ID = p.BrandID WHERE p.Name LIKE '%Microsoft%' OR b.Name LIKE '%Microsoft%' OR p.Name LIKE '%Keyboard%' OR b.Name LIKE '%Keyboard%'

Please help me to write proper MySQL query or any schema change with query..

Sailesh Jaiswal
  • 187
  • 1
  • 17

1 Answers1

1

Appreciate the question. Though I don't have exact answer but surely can discuss one approach to solve the problem.

STEP 1 Get BRAND NAME+NAME as single string.

STEP 2 Tokenise the entered STRING. Example Microsoft Keyboard = Mincrosoft,Keyboard Following Link for spliting the entered data. How do I split a string so I can access item x?

STEP 3 DO a like query on the string obtained in step 1.

Community
  • 1
  • 1
Ashish Agarwal
  • 6,215
  • 12
  • 58
  • 91
  • SELECT * FROM tbl_products p INNER JOIN gm_brands b ON b.ID = p.BrandID WHERE CONCAT(p.Name, ' ', b.Name) LIKE '%Microsoft%' OR CONCAT(p.Name, ' ', b.Name) LIKE '%keyboard%' tried this but no luck – Sailesh Jaiswal Dec 10 '15 at 18:45