sql - UNION ALL where value of second SELECT depends on the first - Stack Overflow

admin管理员组

文章数量:1202392

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.

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.

• sql
• reporting-services

Share Improve this question Follow edited Jan 22 at 9:53 Chait 1,26722 gold badges2222 silver badges3434 bronze badges asked Jan 21 at 19:47 JerryJerry 2155 bronze badges 3

• Put your first query into a CTE and use an EXCEPT or NOT EXISTS in your second query's WHERE clause to filter out the items in your second query that are duplicates of the ones in the CTE. Then UNION them together. – Robert Harvey Commented Jan 21 at 20:02
• Sample data and expected results would really help – Charlieface Commented Jan 21 at 22:17
• Please tag your RDBMS – Dale K Commented Jan 22 at 5:54

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

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 )

You can combine WITH, UNION ALL, and an anti-join, as in:

with 
a as (
  -- first select here
),
b as (
  -- second select here
)
select * from a
union all (
  select * from b
  left join a on a.po_number = b.po_number
  where a.po_number is null
)

You can union first, then group it on the outside and use conditional aggregation. So you don't need to take into account the first half in the second half, instead you just need to look from the outer query where the data is coming from.

Note that your joins don't make much sense: if you left-join then use a WHERE then you effectively get an inner-join. Also don't use LTRIM(RTRIM( if you can, because it prevents using indexes. Fix up the data in the table instead.

SELECT
    u.item_no, 
    ISNULL(
        SUM(CASE WHEN u.IsFromMain = 1 THEN u.qty_inbound END),
        SUM(u.qty_inbound)
    ) AS qty_inbound, 
    0 AS qty_outbound, 
    u.req_ship_dt,
    u.PO_NUMBER
FROM (
    SELECT
        li.ITEM_NUMBER AS item_no, 
        li.ITEM_QTY_ORDERED AS qty_inbound, 
        h.EST_CUSTOMER_DELIVERY_DATE AS req_ship_dt,
        h.PO_NUMBER AS PO_NUMBER,
        1 AS IsFromMain
    FROM            
        cp_po_header h
    JOIN
        cp_line_item li ON h.CP_PO_HEADER_ID = li.LINE_PO_HEADER_ID
    WHERE        
        h.EST_CUSTOMER_DELIVERY_DATE BETWEEN CAST(GETDATE() AS date) AND DATEADD(dd, 364, CAST(GETDATE() AS date))
        AND LTRIM(RTRIM(li.ITEM_NUMBER)) = @item

    UNION ALL

    SELECT        
        l.item_no, 
        l.qty_remaining,
        l.request_dt,
        l.ord_no,
        NULL
    FROM            
        poordlin_sql AS l
    JOIN
        poordhdr_sql AS h ON l.ord_no = h.ord_no
    WHERE
         h.ord_status IN ('P', 'R', 'U')
         AND l.qty_remaining > 0
         AND l.request_dt BETWEEN CAST(GETDATE() AS date) AND DATEADD(dd, 364, CAST(GETDATE() AS date))
         AND l.item_no = @item
) u
GROUP BY
    u.item_no,
    u.req_ship_dt,
    u.PO_NUMBER;                      

本文标签： sqlUNION ALL where value of second SELECT depends on the firstStack Overflow