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
|