You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Patrick Sp." <pa...@gmail.com> on 2007/11/08 18:03:24 UTC
Quering on multiple rows
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.
--
View this message in context: http://www.nabble.com/Quering-on-multiple-rows-tf4772154.html#a13651100
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Quering on multiple rows
Posted by Kristian Waagan <Kr...@Sun.COM>.
Patrick Sp. wrote:
> Hi Stanley,
>
> Thank you for your response.
> This is what is actually done. My question is rather about the SQL statement
> I need to execute in order to be able to express constraints on multiple
> rows on the Mission table related to one specific employee.
> Any help is appreciated.
> Thanks
> P.
Hello Patrick,
Do you mean multiple rows or multiple columns?
Is there a problem with Derby not behaving as expected?
"OR", "AND" and "IN", maybe "NOT"
--
Kristian
>
> ---
> 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).
>
>
>
Re: Quering on multiple rows
Posted by Stanley Bradbury <St...@gmail.com>.
I was confused by you use of 'constraint' - I was thinking of data
integrity constraints implemented by foreign keys or UNIQUE or NOT
NULL. I think Kristian pointed you in the right direction, use one of
the basic or qualified predicates to retrieve the records you wish.
Patrick Sp. wrote:
> Hi Stanley,
>
> Thank you for your response.
> This is what is actually done. My question is rather about the SQL statement
> I need to execute in order to be able to express constraints on multiple
> rows on the Mission table related to one specific employee.
> Any help is appreciated.
> Thanks
> 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).
>
>
>
>
Re: Quering on multiple rows
Posted by "Patrick Sp." <pa...@gmail.com>.
Hi Stanley,
Thank you for your response.
This is what is actually done. My question is rather about the SQL statement
I need to execute in order to be able to express constraints on multiple
rows on the Mission table related to one specific employee.
Any help is appreciated.
Thanks
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).
--
View this message in context: http://www.nabble.com/Quering-on-multiple-rows-tf4772154.html#a13652328
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Quering on multiple rows
Posted by Stanley Bradbury <St...@gmail.com>.
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