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