From: Peter Brawley
Date: May 29 2006 11:18pm
Subject: Re: ugly SQL for a report...
List-Archive: http://lists.mysql.com/mysql/198386
Message-Id: <447B8160.3020807@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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