Hello Trimurthy,
On 4/4/2013 3:21 AM, Trimurthy wrote:
Hi list,
i wrote the following query and it is returning duplicate entries
as shown below, can any one suggest me how to avoid this duplicate entries,
with out using distinct.
Query:
select p.date,p.coacode,p.type,p.crdr,p.quantity,p.amount from
ac_financialpostings p join (select iac from ims_itemcodes where (cat =
'Male Birds' or cat = 'Female Birds')) i on p.coacode = i.iac where p.trnum
like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date
Some more options to the DISTINCT clause may be either EXISTS or IN()
Examples:
select ...
from
ac_financialpostings p
WHERE
exists (select 2 from ims_itemcodes where (cat = 'Male Birds' or cat
= 'Female Birds') and ims_itemcodes.iac = p.coacode)
AND p.trnum like '%02'
AND p.date between '2012-10-04' and '2013-04-04'
order by date
select ...
from ac_financialpostings p
WHERE
p.coacode IN(select iac from ims_itemcodes where (cat =
'Male Birds' or cat = 'Female Birds'))
AND p.trnum
like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date
Or you can use the DISTINCT clause in your subquery, too
select ...
from ac_financialpostings p
join (select DISTINCT iac from ims_itemcodes where (cat =
'Male Birds' or cat = 'Female Birds')) i on p.coacode = i.iac where p.trnum
like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date
Or you can use an explicit temporary table
CREATE TEMPORARY TABLE tmp_iac (key(iac)) ENGINE=MEMORY
SELECT DISTINCT iac
FROM ims_itemcodes
WHERE cat IN('Male Birds', 'Female Birds');
SELECT ...
FROM ac_finanancialpositions p
JOIN tmp_iac
ON tmp_iac.iac = p.coacode
WHERE ...
ORDER BY ... ;
DROP TEMPORARY TABLE tmp_iac;
The advantage to this is that before MySQL 5.6, the implicit temporary
table created by your subquery was not indexed. For more than a trivial
number of rows to compare against, that can reduce overall performance
because the results of your subquery would need to be scanned for each
row of the outer table in the main query it was joining to. Based on the
WHERE clause, all rows from the outer table may not be in the 'join set'
so this is not always a Cartesian product problem.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
