I have a table for all the Sales Orders (SO) for a part number, one for all the Purchase Orders (PO) for a part number. I want to merge the results. Most of the time there will not be the same number of Purchase Orders and Sales Orders per part.
In this example, I have 2 Sales Orders and 1 Purchase order:
SO
Company Part SalesOrder
ABC 123 5530
ABC 123 6854
ABC 456 7772
ABC 456 6868
PO
Company Part PurchaseOrder
ABC 123 9889
ABC 456 9308
ABC 456 9655
ABC 456 9774
I expect:
Company Part SalesOrder PurchaseOrder
ABC 123 5530 9889
ABC 123 6854 NULL
ABC 456 7772 9308
ABC 456 6868 9655
ABC 456 NULL 9774
But see:
Company Part SalesOrder PurchaseOrder
ABC 123 5530 9889
ABC 123 6854 9889
ABC 456 7772 9308
ABC 456 7772 9655
ABC 456 7772 9774
ABC 456 6868 9308
ABC 456 6868 9655
ABC 456 6868 9774
My query:
select coalesce(SO.Company, PO.Company) as Company,
coalesce(SO.Part, PO.Part) as Part,
SO.SalesOrder,
PO.PurchaseOrder
from SO full outer join PO
on SO.Company=PO.Company and SO.Part=PO.Part
Maybe it's not a full outer join that I need.
I looked at posts like SQL Full Outer Join and my desired results look similar and my query looks like the chosen solution, but I am failing.
The PO's and SO's have no relation to each other than they are for the same part. A particular PO is NOT getting created to fulfill a particular SO. Some parts may ONLY have SO's (for example, manufactured parts) some might have ONLY PO's (component parts to a manufactured item). Some parts will happen to have the same number of SO's and PO's by coincidence, but most of the time, there will probably be more than one or the other.
For example, if I wanted to look at a part's historic activity, there might be 4 sales orders for it, and 1 purchase order for it. If I were to do a union where I basically clumped the 'activity' (SO's/Po's) into one column, then for that part, the query would return 5 rows of activity (4 SO's/ 1 PO). But instead of having 1 column and 5 rows, I want 2 columns (one for SO's and one for PO's) and have 4 rows. All rows in the SO column would not be null, and 4 would be null for PO's and one would not. It's just a visual preference to have the first row to contain the PO row that is not null, but in no way are the SO and PO of row one actually related other than that they happen to be on the same row.
Say I have a customer table, and a vendor table, and they both have the field names of 'Name' and 'State' and I want to make a list of all my customers or vendors that are in California.
This:
select c.name, 'Cust' as Type, c.state
from customer c
where c.state='CA'
union
select v.name, 'Vend', v.state
from vendor v
where v.state='CA'
gives something like:
Name Type State
BB Shrimp Cust CA
Vista Inc Cust CA
Mary's Lamb Cust CA
Cali Coffee Cust CA
Cool Guys Cust CA
Tap Corp Vendor CA
Blue Supply Vendor CA
Sun Shore Vendor CA
But I wanted to see this:
Vendor Customer State
Tap Corp BB Shrimp CA
Blue Supply Vista Inc CA
Sun Shore Mary's Lamb CA
NULL Cali Coffee CA
NULL Cool Guys CA
NULL Tap Corp CA
With data presented that way, I could throw it into SSRS and make it look like:
State Vendors Customers
CA Tap Corp BB Shrimp
Blue Supply Vista Inc
Sun Shore Mary's Lamb
Cali Coffee
Cool Guys
Now switch State for Part, Vendor for PO, and Customer for SO, and that's what I am trying to achieve. Vendors and Customers have no relation other than being from the same state; some states might have more vendors than customers or they might have the same, but its unrelated. Same goal with PO and SO.
It seems like you want to pair off POs and SOs based on their sequence, but FULL OUTER JOIN is going to just pair them up in all possible combinations. If you want to capture the order, you need to do a ROW_NUMBER() first:
SELECT COALESCE(SO2.company, PO2.company) AS Company,
COALESCE(SO2.part, PO2.part) AS Part,
so.salesorder,
po.purchaseorder
FROM (SELECT *,
Row_number()
OVER (
partition BY so.part, so.company
ORDER BY so.salesorder) AS SO_Sequence
FROM so) AS SO2
FULL OUTER JOIN (SELECT *,
Row_number()
OVER (
partition BY po.part, po.company
ORDER BY po.purchaseorder) AS PO_Sequence
FROM po) AS PO2
ON SO2.company = PO2.company
AND SO2.part = PO2.part
AND SO2.so_sequence = PO2.po_sequence