Re: Help with simple SQL problem

steelx schrieb am 30-Apr-98 19:55:38
zum Thema "Help with simple SQL problem":

>I'm a newbie when it comes to databases and SQL. I'm now working with Oracle
>7 and am messing around with tables to model a video shop. I've the
>following tables:

>FILM(film_no, film_name, filmmaker_id)
>FILM_MAKER(filmmaker_id, maker_name, maker_address)

>The filmmaker_id in FILM is a foreign key.

>The problem is, how do I find out the film maker that has the most films with
>the video shop?

Hello,

you could try this

select

   fi.filmmaker_id,
   fm.maker_name
from

   film fi,
   filmmaker fm
where fi.filmmaker_id=fm.filmmaker_id
group by fi.filmmaker_id,fm.maker_name
having count(film_no)=(select max(count(film_no))

                       from film
                       group by filmmaker_id);

This is not quite performant but is works. I don't know if it is possible to avoid the subquery above.

Hope that helps,
Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Schulstr. 12            | lothar.armbruester_at_t-online.de
D-65375 Oestrich-Winkel |