MySQL - Search table that contains Foreign key id with foreign key value-CodePudding

I'm making search engine inside my platform, and I want to search specific table called business_services, that table contains a foreign key from another table called services.

The ideas that every business provide some of these services but I only store service ids in business_services table.

how I can search business_services table for a service using only it's id while I'm searching with name

and if that's not possible, I can make another column inside business_services table to store service name but how I can update that name whenever service name changes

business

| id  | name_en | name_fr | status |
|-----|---------|---------|--------|
| 134 | name 1  | nom 1   | 1      |
| 432 | name 2  | nom 2   | 1      |
| 325 | name 3  | nom 3   | 2      |

services

| id | name_en   | name_fr   | status |
|----|-----------|-----------|--------|
| 5  | service 1 | service 1 | 1      |
| 9  | service 2 | service 2 | 1      |
| 4  | service 3 | service 3 | 1      |

business_services

| id | business_id | service_id | status |
|----|-------------|------------|--------|
| 1  | 134         | 5          | 1      |
| 2  | 432         | 9          | 1      |
| 3  | 325         | 4          | 1      |

CodePudding user response：

If you are looking for example for service fr name called service 1.

select
    bs.*, -- all columns from business_services
    
    -- columns from services table
    s.name_en,
    s.name_fr,
    s.status
from
    business_services bs inner join services s on bs.service_id=s.id
where 
    s.name_fr = 'service 1';