Search through foreign key [closed]

Question

I have one table for products in which I have many columns and I also have one column for category_id

category_id is foreign key here which refers to category table primary key

Now suppose if someone search via keyword %category_name% than I want list of product which have their category in foreign key

How to do that? Can anyone guide me on this?

Answer 1

try this, it will work for you

SELECT p.*
FROM products p 
INNER JOIN category c ON p.category_id = c.category_id
WHERE c.category_name LIKE '%search%';

Answer 2

First, you join table product to category using product.category_id as Foreign Key.

Then you filter category_name (in this case I used LIKE and keyword). And last you select columns that you need:

SELECT product.*
FROM product 
    INNER JOIN category ON product.category_id = category.category_id
WHERE category.category_name LIKE '%keyword%'