List:General Discussion« Previous MessageNext Message »
From:Trimurthy Date:April 4 2013 7:21am
Subject:Join query returning duplicate entries
View as plain text  
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  | MORTALITY          | Cr   |        3 |      0 |
| 2012-11-10 | 600500  | MORTALITY          | Cr   |        3 |      0 |
| 2012-11-10 | 600500  | MORTALITY          | Cr   |        6 |      0 |
| 2012-11-10 | 600500  | MORTALITY          | Cr   |        6 |      0 |
| 2012-11-10 | 600500  | MORTALITY          | Cr   |       12 |      0 |
| 2012-11-10 | 600500  | MORTALITY          | Cr   |       12 |      0 |
| 2012-11-17 | 600500  | MORTALITY          | Cr   |       10 |      0 |
| 2012-11-17 | 600500  | MORTALITY          | Cr   |       10 |      0 |
| 2012-11-17 | 600500  | MORTALITY          | Cr   |        6 |      0 |
| 2012-11-17 | 600500  | MORTALITY          | Cr   |        6 |      0 |
| 2012-11-17 | 600500  | MORTALITY          | Cr   |        2 |      0 |
| 2012-11-17 | 600500  | MORTALITY          | Cr   |        2 |      0 |
| 2012-11-17 | 600500  | Birds Amortization | Cr   |        0 | 411939 |
| 2012-11-17 | 600500  | Birds Amortization | Cr   |        0 | 411939 |
| 2012-11-17 | 600500  | MORTALITY          | Cr   |       13 |      0 |
| 2012-11-17 | 600500  | MORTALITY          | Cr   |       13 |      0 |
| 2012-11-24 | 600500  | MORTALITY          | Cr   |       19 |      0 |
| 2012-11-24 | 600500  | MORTALITY          | Cr   |       19 |      0 |
| 2012-11-24 | 600500  | Birds Amortization | Cr   |        0 | 411939 |
| 2012-11-24 | 600500  | Birds Amortization | Cr   |        0 | 411939 |
| 2012-11-24 | 600500  | MORTALITY          | Cr   |       14 |      0 |
| 2012-11-24 | 600500  | MORTALITY          | Cr   |       14 |      0 |
+------------+---------+--------------------+------+----------+--------+
76 rows in set (0.02 sec)

mysql>

             Normal   0               false   false   false      EN-US   
X-NONE   AR-SA                                                              
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
                                                                      
Thanks & Kind Regards,
TRIMURTHY
  

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