Cory,
One way to lose the duplicate queries is to assign the count per pnr.id
to a user var, and calculate via that value:
SELECT
pnr.ID AS ID_pnr,
pnr.reservationdatetime,
pnr.conf_number,
pnr.created_by,
( SELECT @pp_cnt := COUNT(1)
FROM pnr_passengers pp
WHERE pp.ID_pnr = pnr.ID
) AS pp_count,
GROUP_CONCAT(pp.name_last,', ',pp.name_first
ORDER BY name_last DESC SEPARATOR '<br>') AS names,
( SELECT SUM( pf.base_fare * ( @pp_cnt )
FROM pnr_fares pf
WHERE pf.ID_pnr=pnr.ID
) AS base_fare,
( SELECT SUM( pf.tax * ( @pp_cnt )
FROM pnr_fares pf
WHERE pf.ID_pnr=pnr.ID
) AS tax ,
( SELECT SUM(psf.segfee_amount * ( @pp_cnt )
FROM pnr_segments_fees psf
INNER JOIN pnr_segments pss ON psf.ID_segments=pss.ID
WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID ) AS xf ,
( SELECT SUM(amount_value) service_fees
FROM pnr_service_fees
WHERE ID_pnr=pnr.ID
) AS services ,
( SELECT SUM(amount)
FROM pnr_payments_credits
WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale'
) AS payments,
( SELECT SUM(amount)
FROM pnr_payments_credits
WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit'
) AS credits
FROM pnr
INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr
WHERE pnr.reservationdatetime>='2000-05-29 00:00:00'
AND pnr.reservationdatetime<='2006-05-29 23:59:59'
GROUP BY pnr.ID
ORDER BY reservationdatetime;
PB
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 5/29/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
