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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
