MySQL Forums
Forum List  »  Optimizer & Parser

intense query with "union"
Posted by: asif baloch
Date: February 07, 2007 04:54PM

Hi,

I have a query that uses "union". My programmer says its intense and he has no idea what to use as a substitute. Can you please refer me to a substitute or help me rewrite the query. B/w, we are migration to mySQL from pgSQL so take it easy :) . Below is the mySQL query:

(
select
b.vc_username,
b.vc_multisale_status,
a.in_purchaseid as in_purchaseid,
b.in_sponsorid,
b.in_transactionid as in_transactionid,
a.vc_invoiceno as vc_invoiceno,
c.vc_firstname as s_firstname,
c.vc_lastname as s_lastname,
c.vc_username as s_username,
d.vc_productname as vc_productname,
a.in_productid as in_productid,
a.fl_amount as fl_amount,
a.vc_ptype as vc_ptype,
a.in_customerid as in_customerid,
a.dt_date as dt_date,
b.vc_firstname,
b.vc_lastname,
b.vc_company,
1 as in_quantity

from

tbl_purchase_details a,
tbl_customer_master b,
tbl_customer_master c,
tbl_product_master d

where
b.in_sponsorid=c.in_customerid and
a.in_productid=d.in_productid and
a.in_customerid=b.in_customerid and
lower(b.vc_status) != 'deleted' and
b.bl_dispstatus=true
and a.dt_date <= '2007-02-05'
and a.dt_date >= '2007-02-05'
and a.in_customerid=b.in_customerid
and a.bl_showstatus='true')

union

(select
b.vc_username,
b.vc_multisale_status,
a.in_mapid as in_purchaseid,
b.in_sponsorid,
b.in_transactionid as in_transactionid,
'' as vc_invoiceno,
c.vc_firstname as s_firstname,
c.vc_lastname as s_lastname,
c.vc_username as s_username,
d.vc_productname as vc_productname,
5 as in_productid,
a.fl_amount as fl_amount,
'Bill to RFW' as vc_ptype,
b.in_customerid as in_customerid,
a.dt_date as dt_date,
b.vc_firstname,
b.vc_lastname,
b.vc_company,
a.in_quantity
from
tbl_map_purchase a,
tbl_customer_master b,
tbl_customer_master c,
tbl_product_master d
where
lower(b.vc_status) != 'deleted' and
b.bl_dispstatus=true and
a.bl_status=true and
b.in_sponsorid=c.in_customerid and
a.in_productid=d.in_productid and
upper(a.vc_username)=upper(b.vc_username)
and a.dt_date <= '2007-02-05'
and a.dt_date >= '2007-02-05'
and upper(a.vc_username)=upper(b.vc_username)
)
order by in_customerid desc

======================================================================================================================================


select a.*,b.vc_username as spon_username,b.vc_firstname as spon_firstname,b.vc_lastname as spon_lastname from
tbl_customer_temp_master as a,
tbl_customer_temp_master as b where
a.in_customerid != a.in_sponsorid
and a.in_sponsorid=b.in_customerid
and upper(a.vc_joinstatus)=upper('Prejoin')
order by dt_date desc,in_customerid desc


==============================================================================================================================================

select sum(c.in_commission) as a,c.in_mainid,b.in_customerid,b.vc_username,b.vc_firstname,
b.vc_lastname,b.vc_company,b.vc_address,b.vc_address1,b.vc_city,b.vc_email,b.vc_fax,
b.vc_state,b.vc_country,b.vc_postalcode,b.vc_phone,b.vc_mobile,
b.in_ssn,b.vc_ppn,b.vc_status,b.vc_multisale_status from tbl_customer_master as b,tbl_commission_details as c where b.in_customerid=c.in_mainid
and c.dt_date >= '12/26/2006'
and c.dt_date <= '01/25/2007'
and c.in_commforprod in (1,6,7,8,2,3,4,9)
group by
c.in_mainid,b.in_customerid,b.vc_username,b.vc_firstname,b.vc_firstname,
b.vc_lastname,b.vc_company,b.vc_address,b.vc_address1,b.vc_city,
b.vc_state,b.vc_country,b.vc_country,b.vc_postalcode,b.vc_phone,b.vc_mobile,
b.in_ssn,b.vc_ppn,b.vc_status,b.vc_multisale_status,b.vc_email,b.vc_fax


many thanks.

Baloch

Options: ReplyQuote


Subject
Views
Written By
Posted
intense query with "union"
2512
February 07, 2007 04:54PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.