Re: Need help for writing a query

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.
>
>
>
Received on Mon Nov 13 2000 - 07:04:06 CST