db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Quering on multiple rows
Date Thu, 08 Nov 2007 17:46:19 GMT
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


Mime
View raw message