I'm looking to report on orders that contain a specific subclass. The orders can have multiple subclasses on them if a customer orders more than 1 item per order. I want to retrieve the results of orders with a specific subclass but i also want the data of any other product on the customers orders. The way i currently have it written, my answerset only gives me lines where the specific subclass exists. If i want subclass APPLE but there's also SUBCLASS Marvel Movies, my answerset only gives a row of APPLE. I'd have to look at the order by itself to know it had other lines on it. I also only want subclasses or lines that have a specific modification present.
select distinct
ORD_ID,
ORD_DT,
reship,
SKU,
SUBCLASS
from
TBEND_OR_ORD ord1
JOIN TBEND_OR_ORDER_It item on ord1.ORD_ID = item.ORD_ID
INNER JOIN TBEND_OR_ORD_AUD aud ON ORD1.ORD_ID = aud.ORD_ID
LEFT JOIN PVWBB_LU_ITEM_ECM sku ON ITEM.OFFR_KEY = sku.OFFR_KEY
where
aud.src_rec like ANY ('allow%', 'RESHIP%') and ORD_DT >= '2023-08-21'
and
SUBCLASS in ('NINTENDO SWITCH HDWE',
'STANDARD APPLE WATCH',
'BEATS ON/OVER EARS',
'APPLE WIFI',
'APPLE TRUE WIRELESS',
'STANDALONE VR',
'GSRF APPLE HEADPHONE')
I've tried a few things, but nothing has worked