problems with duplicate records in query results

[ReK410]

I know that the data would be unfamiliar to most but is there anything as far as my query syntax that would result in duplicate records? I'm getting the same row in my results 3 times!



select p21_ar_view.invoice_no, P21_ar_view.line_no, p21_order_view.assembly, P21_ar_view.item_id, P21_ar_view.qty_shipped, P21_ar_view.qty_shipped,
P21_ar_view.unit_of_measure, P21_ar_view.unit_price_home, P21_ar_view.commission_cost_home, P21_ar_view.commission_cost_home,
P21_ar_view.commission_cost_home, P21_ar_view.tax_item, P21_ar_view.tax_amount_home, P21_ar_view.qty_shipped, P21_ar_view.qty_shipped,
p21_ar_view.item_desc, p21_order_view.product_group_id, p21_order_view.supplier_id, p21_order_view.requested_date, p21_order_view.required_date,
OE_HDR_SALESREP.salesrep_id, p21_ar_view.order_no
from P21_ar_view
join P21_order_view on P21_ar_view.order_no = P21_order_view.order_no
join OE_HDR_SALESREP on P21_ar_view.order_no = oe_hdr_salesrep.order_number
where
P21_ar_view.order_date > '2007-03-05' and P21_ar_view.order_date < '2009-12-31' and p21_order_view.completed = 'Y'
and p21_ar_view.order_no > '1000000' and p21_ar_view.order_no < '2000000' and oe_hdr_salesrep.primary_salesrep = 'Y' and p21_order_view.assembly <> 'B'
and P21_order_view.hdr_delete_flag = 'n' and p21_order_view.completed = 'Y' and p21_order_view.quote_flag = 'N' and P21_order_view.hdr_cancel_flag = 'n'
order by P21_ar_view.invoice_no

 

[r937]

the problem undoubtedly lies here --

from P21_ar_view join P21_order_view on P21_ar_view.order_no = P21_order_view.order_nojoin OE_HDR_SALESREP on P21_ar_view.order_no = oe_hdr_salesrep.order_number

from the sounds of it (same row 3 times), there is a one-to-many relationship in there that you aren't aware of



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon