Re: Join query returning duplicate entries
- Original Message - From: Lucky Wijaya luckyx_cool_...@yahoo.com To: mysql@lists.mysql.com Sent: Thursday, 4 April, 2013 10:51:50 AM Subject: Re: Join query returning duplicate entries Hi, sorry i tried to help but i hardly understand the use of join in your query since the joined table is not used anywhere. Of course it's used - the joined subquery limits the items to male or female birds. As for an answer to your question, Trimurthy, just use SELECT DISTINCT rest of your query. The cause of the duplicates may be that the iac for male and female birds is identical. -- Linux Kriek Wanderung April 19-21, 2013 http://www.tuxera.be/lkw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Join query returning duplicate entries
Hi, sorry i tried to help but i hardly understand the use of join in your query since the joined table is not used anywhere. From: Trimurthy trimur...@tulassi.com To: mysql@lists.mysql.com Sent: Thursday, 4 April 2013, 14:21 Subject: Join query returning duplicate entries 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 Output: ++-++--+--++ | date | coacode | type | crdr | quantity | amount | ++-++--+--++ | 2012-10-06 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 22 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 22 | 0 | | 2012-10-06 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-06 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-06 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 15 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 15 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-13 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | MORTALITY | Cr | 1 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 14 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 14 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-20 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | MORTALITY | Cr | 10 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 10 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 1 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-27 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-27 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-27 | 600500 | MORTALITY | Cr | 11 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 11 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-03 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-10 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-10 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-10 | 600500 | MORTALITY | Cr | 6 | 0 | | 2012-11-10 | 600500 | MORTALITY | Cr | 6 | 0 | | 2012-11-10 | 600500 |
Re: Join query returning duplicate entries
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