Re: nested select within a DISTINCT block

On Thu, Sep 14, 2006 at 05:02:25 -0700, zqzuk <ziqi.zhang@hotmail.com> wrote:
> 
> Hi, here i have a problem with this task...
> 
> I have a table "cancellation" which stores cancelled bookings and details of
> charges etc
> and a table "bookings" which stores details of bookings, for example:
> 
> cancellation(cancellation_id, booking_id, charge)
> booking(booking_id, customer_id, product_package_id, details)
> 
> in the query, i wish to find, how many customers have booked for each
> product_package_id. if there were 3 bookings for product_package_id=1, and
> all these are cancelled and therefore exist in cancellation, then the query
> result shoud display something like
> 
> could anyone give any hints please, many thanks !

Left join booking and cancellation, select rows where cancellation_id
IS NULL, GROUP BY either booking_id, and return that booking_id and count(*).