Re: Simple Query?

"jop" <jop_at_novabase.pt> wrote in message news:kuLQ6.5994$ZG.17990766_at_newsserver.ip.pt...
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:th6qg3qre4l96f_at_beta-news.demon.nl...
> >
> > "jop" <jop_at_novabase.pt> wrote in message
> > news:jQIQ6.5963$gR.18202769_at_newsserver.ip.pt...
> > > I do I select all the customers from the same city?
> > > I thought it would be like this:
> > >
> > > SELECT C.Name, C.City
> > > FROM Customer C, Customer Ca
> > > WHERE C.City = Ca.City
> > >
> > > But I get an unexpected result.
> > >
> > >
> > >
> >
> > select c.name, c.city
> > from customer c
> > where c.city = '<city name>'
> >
> I think I didin't explain myself very well. I want all Names that have
 City
> in common. If a customer is the only one in that city I don't want it.
>
> > or
> > select c.name, c.city
> > from customer c
> > order by c.city
> >
> > Your query will result in a so-called cartesian product
> > (you get all m times n combinations)
> > You might want to consider buying the book of Rick van der Lans on SQL
 the
> > structured query language available at Amazon, first published in 1986
 and
> > still reprinted, originally Dutch.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >
> >
>
>

select c.name, c.city
from customer c
where c.city in
(select c.city
 from customer
 group by city
 having count(distinct name) > 1
)

Hth,

Sybrand Bakker, Oracle DBA Received on Tue May 29 2001 - 08:58:10 CDT