Left Outer Join while eliminating Duplicates
-
I have a Sales table and a PO table that I want to Join. I want to do a Left Outer Join on Sales. When I use this syntax I get my join, but get extra records, because there could be multiple PO records that match to a Sales Record
select a.*, b.pob_system_date, b.pob_company, b.pob_site, b.pob_po_nbr, b.pob_line_nbr
from SALES a
left join PO b
on a.company = b.pob_so_company and
a.site = b.pob_so_site and
a.ordernbr = b.pob_so_order_nbr and
a.ord_dist = b.pob_so_dist_nbr and
a.ord_ship = b.pob_so_ship_nbr and
a.linenbr = b.pob_so_line_nbr and
a.invdate >= b.por_receipt_date
What I am trying to do is filter out the duplicates, and join on only the record that has the highest PO.POR_RECEIPT_DATE prior to the SALES.INVDATE. Here is one attempt I made at doing this, I get the correct number of rows, but all of my PO data is null. I have a feeling I may be missing something simple, but I can not see it
select a.*, b.pob_system_date, b.pob_company, b.pob_site, b.pob_po_nbr, b.pob_line_nbr
from SALES a
left join PO b
on a.company = b.pob_so_company and
a.site = b.pob_so_site and
a.ordernbr = b.pob_so_order_nbr and
a.ord_dist = b.pob_so_dist_nbr and
a.ord_ship = b.pob_so_ship_nbr and
a.linenbr = b.pob_so_line_nbr and
b. POR_RECEIPT_DATE = (select max(POR_RECEIPT_DATE) from PO
where a.company = b.pob_so_company and
a.site = b.pob_so_site and
a.ordernbr = b.pob_so_order_nbr and
a.ord_dist = b.pob_so_dist_nbr and
a.ord_ship = b.pob_so_ship_nbr and
a.linenbr = b.pob_so_line_nbr and
a.invdate >= b.por_receipt_date)
Here is another attempt and it also came up with Null
select a.*, b.pob_system_date, b.pob_company, b.pob_site, b.pob_po_nbr, b.pob_line_nbr
from SALES a
left join PO b
on a.company = b.pob_so_company and
a.site = b.pob_so_site and
a.ordernbr = b.pob_so_order_nbr and
a.ord_dist = b.pob_so_dist_nbr and
a.ord_ship = b.pob_so_ship_nbr and
a.linenbr = b.pob_so_line_nbr and
b. POR_RECEIPT_DATE = (select max(POR_RECEIPT_DATE) from PO
where a.invdate >= b.por_receipt_date
group by b.pob_so_company, b.pob_so_site, b.pob_so_order_nbr,
b.pob_so_dist_nbr, b.pob_so_ship_nbr, b.pob_so_line_nbr)
Does anyone have a suggestion how I can accomplish this?
Thanks,
TJP8
-
Not sure if this will work as advertised, but it is worth a try:
select
a.*,
b.pob_system_date,
b.pob_company,
b.pob_site,
b.pob_po_nbr,
b.pob_line_nbr
from
dbo.SALES a
left join dbo.PO b
on (a.company = b.pob_so_company and
a.site = b.pob_so_site and
a.ordernbr = b.pob_so_order_nbr and
a.ord_dist = b.pob_so_dist_nbr and
a.ord_ship = b.pob_so_ship_nbr and
a.linenbr = b.pob_so_line_nbr)
where
a.invdate >= (select
max(c.por_receipt_date)
from
dbo.PO c
where
a.ordernbr = c.pob_so_order_nbr
and a.company = c.pob_so_company
and a.site = c.pob_so_site)
Let me know if it does what you are looking for!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply