Re: Video Shop SQL problem

I didn't test this, but I believe it's right:

select b.film_no, a.film_name, count(distinct buyer_no) from film a, film_copy b
where a.film_no = b.film_no
group by b.film_no, a.film_name
having count(distinct buyer_no) = (select count(*) from film_buyer);

I used 'count(distinct buyer_no)' because I didn't know if a buyer could buy a film more than once.

Let me know if it works.
-Eric

steelx_at_hotmail.com wrote:

> In article <354c554f.3393342_at_news.dircon.co.uk>#1/1,
> jeremyr_at_techie.com wrote:
> >
> > steelx_at_hotmail.com wrote:
> >
> > >I'm new to SQL and Oracle. I'm currently practising my SQL by modelling a
> > >fictitious video shop, and I hope someone out there will help me with the
> > >following problem.
> > >
> > <table layouts snipped>
> >
> > >Is there any way to find out which films are purchased by all buyers using
> > >SQL?
> >
> > Sure there is - but if you clarify the question a little, it would
> > help. Do you want films purchased by any buyers? Only films where ALL
> > buyers have purchased a copy? Or something else ...
> >
> > Jeremy
> >
>
> I would like to know films where ALL buyers have purchased a copy. Yeah, it
> seems unlikely in real life, but I just wanna know if that's possible.
>
> El Ti
> steelx_at_hotmail.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Tue May 19 1998 - 11:54:05 CDT