List:General Discussion« Previous MessageNext Message »
From:shawn green Date:April 4 2013 2:33pm
Subject:Re: Join query returning duplicate entries
View as plain text  
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
Thread
Join query returning duplicate entriesTrimurthy4 Apr
  • Re: Join query returning duplicate entriesLucky Wijaya4 Apr
    • Re: Join query returning duplicate entriesJohan De Meersman4 Apr
  • Re: Join query returning duplicate entriesshawn green4 Apr