Re: nested select within a DISCTINCT block

On 14 sep 2006, at 23.58, Daryl Richter wrote:
> create table booking(booking_id int, customer_id int,
> product_package_id
> int, details text);
>
> create table cancellation(cancellation_id int , booking_id int, charge
> decimal);
>
> insert into booking values( 1, 1, 1, 'Cxl Booking 1' );
> insert into booking values( 2, 2, 1, 'Cxl Booking 2' );
> insert into booking values( 3, 2, 1, 'Ok Booking 3' );
> insert into booking values( 4, 3, 2, 'Cxl Booking 4' );
>
> insert into cancellation values( 1, 1, 1.00 );
> insert into cancellation values( 2, 2, 1.00 );
> insert into cancellation values( 3, 4, 1.00 );
>
>
> select distinct product_package_id,
> ( select count(booking_id)
> from booking b2
> where
> b2.product_package_id = b1.product_package_id
> and not exists ( select 1 from cancellation c where
> c.booking_id =
> b2.booking_id ) ) as uncancelled_bookings
> from booking b1
> order by product_package_id;
>
> product_package_id uncancelled_bookings
> --------------------- -----------------------
> 1 1
> 2 0

Given the above, you could also phrase it a little more natural, as
follows:

SELECT product_package_id, COUNT(b.booking_id)-COUNT(c.booking_id) AS
un_cancelled_bookings
FROM booking b
LEFT JOIN cancellation c USING(booking_id)
GROUP BY product_package_id
ORDER BY product_package_id;

I don't know about the amount and distribution of data in this case,
but I think this will also give you a slightly better plan in most
cases.

Sincerely,

Niklas Johansson