I'm trying to use a query to provide a list of inventory items and their original sales date.
The first table SOP2 has all orders and the rows are by each item sold for the order.
The second table SOP1 is the order total with a single row per order.
The final table IV1 has most but not all item numbers that are in the SOP2 table
The problem is that when I run my query filtering out some items based on their category in IV1 and type from SOP1 I don't believe the results contain all the unique items that were sold are in the SOP2 table after the filter.
Below is my query
What should I be doing differently?
select min(sop1.docdate) as 'DocumentDate'
,max(sop2.itemnmbr) as 'ItemNumber'
,max(sop2.itemdesc) as 'ItemDescription'
from sop10200_sop30300_Union sop2
join sop10100_sop30200_Union sop1 on sop1.sopnumbe=sop2.sopnumbe
right join iv00101 iv1 on iv1.itemnmbr=sop2.itemnmbr
group by sop2.itemnmbr
order by sop2.itemnmbr