Double filtering program, need some help, sorry for vagueness

[Rohlan]

Please consider the following two tables:

 

People

 

 

   

ID

   

NAME

   

SERVICES_PROVIDED

 

 

   

1

   

John

   

Cleaning | Gardening

 

 

   

2

   

Mary

   

Gardening

 

 

   

3

   

Jack

   

Electrician | Plumbing

 

 

   

4

   

Jane

   

Gardening | Cooking

 

 

 

 

 

Services

 

 

   

ID

   

NAME

 

 

   

1

   

Cleaning

 

 

   

2

   

Gardening

 

 

   

3

   

Electrician

 

 

   

4

   

Plumbing

 

 

   

5

   

Cooking

 

 

At a certain point in my application, I need to be able to select both a worker and a service he/she provides. Obviously it can't be mismatched, Mary can't be assigned a Plumbing job because its not a service she provides. Therefore I'm building a selection program that lets the manager select a Service first, and then the program displays the people who do that job with

 

SELECT *

FROM `people`

WHERE `services_provided` LIKE '%EXAMPLE%'

 

But what if I need a way to specify the person first to obtain their services? Using these two tables, how am I supposed to be able to tell what services are provided by, for example, Jack with just one query?

 

Another thing, I realize I might be better off using service IDs instead of their name in the People table, but if if by chance I can't (I don't own the database nor can I change it without permission), I might be in trouble if there happens to be a service called "Cooking Assistant", and then when searching for %Cooking% I'd run into a bit of a problem because it would display both those who provide the "Cooking" service and the "Cooking assistant" service which wouldn't be the ideal scenario.

 

Thank you for your precious time.

[fenway]

You need a many-to-many table, not delimited fields.