Search code examples
mysqlsqlstringjoinsql-like

Sql reverse search for products and discounts

I have two table, one for products, and one for discounts. Discounts table look like:

ID | Name         | Percentage | Product Name Search Param
-----------------------------------------------------------
1  | Black Friday | 50         | a
2  | White Friday | 75         | b

Product table look like:

ID | Name         | Price 
--------------------------
1  | Shoes        | 2500 
2  | Hat          | 1500
3  | Baseball cap | 500

If the Product name contain Discount.Product Name Search Param value, then the Product get the Discount percentage. I get the the Products from Discount

SELECT * FROM Products WHERE name LIKE '%a%'

But i don't have any idea how to get one Product Discounts.

I want to get the Product with ID 2 which discount is due.

Solution

  • You can join with a like condition:

    select p.*, d.*
from product p
inner join discounts d 
    on p.name like concat('%', d.product_name_seach_param, '%')

    Note: as far as concerns, your design does not look good. It would be better to have a referential tables for products and one for discounts, and a junction table that relates products to discounts.