Patrick Sp. wrote:
Hi,
I have a schema that generates a set of tables with the following
relationships:
An employee may have several Missions.
E.g. 'Svendson' is related to 'New York' , 'Vegas' and 'Boston'
Table Employee
Employee_ID Name
----------- --------
01 Hansen
02 Svendson
03 Black
04 Pettersen
Table Missions
Mission_ID City Employee_ID
-------------- ----- ------------------
1 Atlanta 01
2 New York 03
3 Vegas 03
4 Boston 02
5 Boston 03
I need to perform a search that extract all the employees with constraints
on multiple values from the table 'Missions' such as
Employee.Name='Svendson'
and
that has in Missions.City all 'New York', 'Vegas' and 'Boston'
How to do that in SQL.
Thanks for your help.
P.
In many ways this is a matter of preference but, IMHO, since both
Missions and Employee seem to be primary objects and there could be a
M:M relationship I would create an association table with two columns
Mission_id and Employee_id and, for good form, throw in a
auto-increment column to be used as the primary key of the table (or the
compound key Mission_id and Employee_id could be used as the PK).
I
