Re: Need help for writing a query

In our last gripping episode "spencer" <spencerp_at_swbell.net> wrote:
> you could try something like this to return
> a result set:
>
> select player from
> ( select player
> from player_games
> where game in ('FOOTBALL','RUGBY')
> )
> having count(*) > 1
> group by player
>
> note that this query may return a player, for example,
> that does not play RUGBY, but does have two or more
> rows with FOOTBALL.
>
> something like this should fix that small problem:
>
> select player from
> (
> select player
> from player_games
> where game = 'FOOTBALL'
> group by player
> union all
> select player
> from player_games
> where game = 'RUGBY'
> group by player
> )
> having count(*) > 1
> group by player
>
> "Khemraj DOOKHEE" <kdookhee_at_capgemini.fr> wrote in message
 news:8uolds$gfh$1_at_s1.read.news.oleane.net...
> > Hi everyboby,
> >
> > I've got a table named 'PLAYER_GAMES' with the following data
 structure :
> > (PLAYER varchar2(10), GAME varchar2(25)).
> > The table is filled as follows :
> >
> > PLAYER GAME
> > --------------------------------------------
> > TOM FOOTBALL
> > PETER BASKET-BALL
> > PETER TENNIS
> > PAUL VOLLEY-BALL
> > PAUL TENNIS
> > TOM BASKET-BALL
> > TOM RUGBY
> > STEVE FOOTBALL
> > STEVE RUGBY
> >
> >
> > Can anyone help me in writing the query which gives the name of
 players who
> > play BOTH football and rugby ?
> >
> > Thanks in advance,
> > Khemraj.
> >
> >
> >
>
>

Why go through all of that work when the following returns the proper result set:

select player
from player_games
where game = 'FOOTBALL'
union
select player
from player_games
where game = 'RUGBY'

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.