I have a query that is attempting to join a few tables. I'm struggling because there is an inv_mast table that holds all of our part numbers with a key bein inv_mast_uid and we also have a table for substitute part numbers which is inv_sub and the key there is inv_sub.sub_inv_mast_uid. This is how these tables are connected. Now I have a multiple part numbers in our system that can substitute to the same part number and so each time I attempt this query it will give me multiple duplicates because that is how many other part numbers substitute to that number. Does anyone know a way I can create a join that will eliminate these duplicates and only return the substitute for the specific order that I am attempting to query for? I apologize if I'm not being completely thorough and specific here. I have the query below..
Select distinct
oh.order_no,
opt.pick_ticket_no,
im.item_id,
pvi.item_id 'Sub Item ID',
pvi.sub_item_id,
CAST(ol.qty_on_pick_tickets as int) 'Qty'
from oe_pick_ticket as opt
join oe_hdr as oh
on oh.order_no = opt.order_no
join oe_pick_ticket_detail as optd
on optd.pick_ticket_no = opt.pick_ticket_no
INNER JOIN oe_line as ol ON ol.line_no = optd.oe_line_no
AND ol.order_no = opt.order_no AND ol.detail_type <> 3
join inv_mast as im
on im.inv_mast_uid = ol.inv_mast_uid
join inventory_supplier as invsup
on invsup.inv_mast_uid = im.inv_mast_uid
join inventory_supplier_x_loc as isx
on isx.inventory_supplier_uid = invsup.inventory_supplier_uid
join customer as cu
on cu.customer_id = oh.customer_id
join oe_hdr_ud as ohu
on ohu.order_no = oh.order_no
join ship_to as st
on st.customer_id = cu.customer_id
join p21_view_inv_sub as pvi
on pvi.sub_item_id = im.item_id
left join inv_sub_ud as isu
on isu.sub_inv_mast_uid = pvi.sub_inv_mast_uid
left join inv_sub on
inv_sub.inv_mast_uid = isu.sub_inv_mast_uid
where (isx.primary_supplier = 'Y')
and (invsup.delete_flag = 'N')
and (im.default_sales_discount_group not like 'T-%')
and opt.pick_ticket_no =
(select MAX(pick_ticket_no)
from oe_pick_ticket
join oe_hdr_ud
on oe_hdr_ud.order_no = oe_pick_ticket.order_no
join oe_hdr
on oe_hdr.order_no = oe_pick_ticket.order_no
join customer
on customer.customer_id = oe_hdr.customer_id
join ship_to
on ship_to.customer_id = customer.customer_id)
Here are my results...
