Re: Simple SQL Question
Date: Sat, 18 Mar 2000 20:39:26 GMT
Message-ID: <8b0phr$as5$1_at_nnrp1.deja.com>
In article <38d2fac0_1_at_news.aei.ca>,
"Vinh Duong" <vduong_at_aei.ca> wrote:
> Hi,
>
> I have the following tables:
>
> Employee (eid, ename, street, city)
> Works (eid, bid, cname,salary)
> Company (bid, cname,city)
> Manages (eid, mid, bid, cname)
>
> A company, which is uniquely identified by its cname, may have
branches
> (indicated by bid) in more than one city.
>
> Help me the write TWO appropriate queries that each satisfy the
following
> requirements:
>
> 1- Find the names of companies that are located IN EVERY CITY in which
'Bank
> of Trade' is located.
>
> 2-Make a list of the cities which have one of its residents as a
manger IN
> EVERY COMPANY located in the city.
>
> Gracias,
>
> Vinh
> Montreal
>
>
Vinh,
The clue is to reformat the condition
"has to conform to a variable number of conditions"
to
"must not violate any condition out of a variable number of conditions"
So you may use an anti-join (where not exists).
For the first question (I had no chance to test it, perhaps the syntax is not ok):
select distinct c1.cname from company c1
where not exists /* there must be no violated condition */
(select city from company c2 where c2.cname = 'Bank of Trade'
/* all the cities BoT resides in */
minus
select city from company c3 where c3.cname = c1.cname
/* all the cities the (current) c1-company resides in */
)
/* if the c1-company resides at least in all cities that
BoT does, the result of the minus will be empty -> c1-company will be selected*/ ;
I did not fully understand the second question, but I think the same technique will be appropriate, because of the IN EVERY-term.
HTH
Pascal Glauser
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Mar 18 2000 - 21:39:26 CET