[SOLVED] UNION ALL where value of second SELECT depends on the first

UNION ALL where value of second SELECT depends on the first

I have 2 SELECT statements linked by a UNION ALL. How would I make the second SELECT omit everything where the PO_NUMBER is the same as in the first?

A side note here - the first SELECT looks at a DB that is more current as far as dates/times but if it hasn't been updated yet we need to look at the original customer order. We don't want it duplicated, so if the PO exists in the first select don't include it in the second.

--/ inbound shipments from Table 1 /
SELECT        
    cp_line_item.ITEM_NUMBER AS item_no, 
    SUM(cp_line_item.ITEM_QTY_ORDERED) AS qty_inbound, 
    0 AS qty_outbound, 
    cp_po_header.EST_CUSTOMER_DELIVERY_DATE AS req_ship_dt,
    cp_po_header.PO_NUMBER AS PO_NUMBER
FROM            
    cp_po_header 
LEFT OUTER JOIN
    cp_line_item ON cp_po_header.CP_PO_HEADER_ID = cp_line_item.LINE_PO_HEADER_ID
WHERE        
    (cp_po_header.EST_CUSTOMER_DELIVERY_DATE BETWEEN CAST(GETDATE() AS date) AND DATEADD(dd, 364, CAST(GETDATE() AS date))) AND (LTRIM(RTRIM(cp_line_item.ITEM_NUMBER)) = '@item')
GROUP BY cp_line_item.ITEM_NUMBER, cp_po_header.EST_CUSTOMER_DELIVERY_DATE, cp_line_item.ITEM_QTY_ORDERED, cp_po_header.PO_NUMBER
                      
UNION ALL

--/  inbound shipmnts from Table 2 /

SELECT        
    l.item_no, 
    SUM(l.qty_remaining) AS qty_inbound, 
    0 AS qty_outbound, 
    l.request_dt AS req_ship_dt,
    l.ord_no AS PO_NUMBER
FROM            
    poordlin_sql AS l 
INNER JOIN
    poordhdr_sql AS h ON l.ord_no = h.ord_no 
LEFT OUTER JOIN
    cp_line_item AS p ON LTRIM(RTRIM(l.ord_no)) = LTRIM(RTRIM(p.PO_NUMBER)) AND p.MACOLA_LINE_NO = l.line_no
WHERE        
    (h.ord_status IN ('P', 'R', 'U')) AND (l.qty_remaining > 0) AND (p.PO_NUMBER IS NULL) AND (l.request_dt BETWEEN CAST(GETDATE() AS date) AND DATEADD(dd, 364, CAST(GETDATE() AS date))) AND (l.item_no = @item)
GROUP BY l.item_no, l.request_dt, l.ord_no

So in summary only lines in the second SELECT are included when PO numbers do not match any lines from the first select.

Solution

Like mentioned in the comment you can use a NOT Exists in the second query.

PO_NUMBER is fetched from cp_po_header table in first query and cp_po_header is also left joined, which means primary source of PO_NUMBER is cp_po_header in first query, based on this you can try something like this

<first query>
UNION ALL
--second query
select col1,col2
where <condition>
AND NOT EXISTS
 ( SELECT 1 FROM cp_po_header cp WHERE 
cp.PO_NUMBER = l.ord_no )