intense query with "union"
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