Re: Simple SQL Question

From: Pascal Glauser <glauser_at_my-deja.com>
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

Original text of this message