CODEWITHSUNDEEP
mysqlsqlpostgresqljoininner-join
Bipin
Bipin

Reputation: 354

Join query returns duplicate rows

purchase_request_master

prm_voucher_no| project_id| status_id|  request_date                        
17                 46           3      11-6-2016 0:00                       
18                 46           3      20-6-2016 0:00                       
19                 46           3      216-2016 0:00

purchase_request_details

prm_voucher_no| item_id|    request_quantity                            
17                80         50                         
17                81         100                            
18                80         75                         
19                83         10                         
19                81         35                         
19                82         120

purchase_order_master

pom_voucher_no| prm_request_id  |supplier_id                            
16                  17              14                          
17                  18              14                          
18                  19              15

purchase_order_details

pom_voucher_no| approved_quantity|  rate                            
16                     50           1000                            
16                     100          1500                            
17                     75           150                         
18                     10           2500                            
18                     35           3000                            
18                     120          1700

when I run the below query it gives 14 rows(duplicate row returning).expected out put row is 6.. Please refer below output tables.. 

select prm.prm_voucher_no,prm.project_id,prm.status_id,prd.requested_quantity,prd.item_id,pom.pom_voucher_no,pom.supplier_id,pod.rate,pod.approved_quantity 
from purchase_request_master prm
left join purchase_request_details prd on prd.prm_voucher_no=prm.prm_voucher_no
left join purchase_order_master pom on prm.prm_voucher_no=pom.request_id
left join purchase_order_details pod on pom.pom_voucher_no=pod.pom_voucher_no
where prm.project_id=46 and ( EXTRACT(MONTH FROM prm.request_Date)=6) and (EXTRACT(YEAR FROM prm.request_Date)=2016) 
group by prm.voucher_no,prm.project_id,prm.status_id,prd.requested_quantity,prd.item_id,pom.voucher_no,pom.supplier_id,pod.rate,pod.approved_quantity 
order by prm.voucher_no

i tried inner join,distinct,distinct least,group by,temporary table,with clause all these method.. but no use every this gives duplicate row

How to solve this problem..

OUTPUT

prm_voucher_no| project_id| status_id|item_id|request_quantity |pom_voucher_no| supplier_id|approved_quantity |  rate   
   17             46           3         80       50                 16               14       100               1000   
   17             46           3         81       100                16               14       75                1500   
   17             46           3         80        75                16               15       10                150    
   17             46           3         81        10                16               14       35                10 
   18             46           3         81        35                17               14       120               35 
   19             46           3         80        120               18               15       50                120    
   19             46           3         81        50                18               14       100               1000   
   19             46           3         82        100               18               14       75                1500   
   19             46           3         80        75                18               15       10                150    
   19             46           3         81        10                18               14       35                10 
   19             46           3         82        35                18               14       120               35 
   19             46           3         80        120               18               15       35                120    
   19             46           3         81        35                18               14       50                1500   
   19             46           3         82        50                18               15       100               1700

EXPECTED OUTPUT

prm_voucher_no| project_id| status_id|  item_id|    request_quantity|   pom_voucher_no| supplier_id|approved_quantity|  rate    
    17             46          3          80             50                  16             14           100            1000    
    17             46          3          81             100                 16             14           75             1500    
    18             46          3          81             35                  17             14           120              35    
    19             46          3          80             120                 18             15           50              120    
    19             46          3          81             50                 18              14          100             1000    
    19             46          3          82             100                 18             14          75              1500

Upvotes: 1

Views: 1469

Answers (1)

Hambone
Hambone

Reputation: 16377

I think the problem is in your data model itself. Ideally, you would have a line_number field in both of your "detail" tables, and this would be used in the join:

create table purchase_request_details (
    prm_voucher_no integer,
    prm_voucher_line integer,    // Add this
    item_id integer,
    request_quantity
)

create table purchase_order_details (
    pom_voucher_no integer,
    pom_voucher_line integer,    // and this
    approved_quantity integer,
    rate integer
)

And then this query would give you the results you seek:

select
  prm.prm_voucher_no,prm.project_id,prm.status_id,prd.request_quantity,
  prd.item_id,pom.pom_voucher_no,pom.supplier_id,pod.rate,pod.approved_quantity 
from
  purchase_request_master prm
  left join purchase_request_details prd on 
    prd.prm_voucher_no=prm.prm_voucher_no
  left join purchase_order_master pom on 
    prm.prm_voucher_no=pom.prm_request_id
  left join purchase_order_details pod on 
    pom.pom_voucher_no=pod.pom_voucher_no and
    prd.prm_voucher_line = pod.pom_voucher_line   // This is the key
where
  prm.project_id=46 and
  EXTRACT(MONTH FROM prm.request_Date) = 6 and
  EXTRACT(YEAR FROM prm.request_Date) = 2016
order by prm.prm_voucher_no

If you have no ability to control the data model, then I think the best you can do is artificially add a line number. I don't recommend this at all, as you are presupposing a lot of things, most notably that the order of records in the one table automatically correlates to the order of records in the other -- and I'm betting that's far from a guarantee.

Adding a line number would be done using the row_number() analytic, and PostgreSQL has that but MySQL does not... you have both tags in your question. Which DBMS are you using?

If you can't add line numbers, can you add item_id to your purchase_order_details table? This would likely handle your issue, unless you can have the same item on multiple lines within a purchase request/order.

In the data you have above, a join on the requested quantity (prd.request_quantity = pod.approved_quantity) fixes your issue, but I am highly confident that this would burn you when you started running it against real data.

Upvotes: 2

Related Questions