SQL Merge tables and return oldest record-CodePudding

What I'm trying to achieve is to merge two tables and return the oldest record, then join it to another table.

When I run the sub query on it's own it works ok, I know rows exist in the subquery. However, when I run it together, I get null in the column First Seen when joined to the main table. I'm struggling to understand why I'm getting null as the tag exists in table I and the tag exists in table FS.

SELECT  
    I.location_id, I.sku_id, I.description, I.qty_on_hand, 
    I.lock_status, I.tag_id, 
    NVL(i.condition_id, ' ') Condition,
    TO_CHAR(FS.dstamp, 'DD/MM/YYYY') AS "First Seen"
FROM 
    inventory I
LEFT JOIN 
    (SELECT TAG_ID, dstamp 
     FROM
         (SELECT tag_id, dstamp 
          FROM inventory_transaction 
          WHERE code IN ('Receipt', 'Stock Check')
          UNION
          SELECT tag_id, dstamp 
          FROM inventory_transaction_archive 
          WHERE code IN ('Receipt', 'Stock Check')) itl
     WHERE ROWNUM = 1) FS ON I.TAG_ID = FS.TAG_ID

This is the result the above query returns:

CodePudding user response：

The trick is sorting by dtstamp column within ROW_NUMBER() Analytic function in order to filter out the oldest records such as

WITH fs AS
(
 SELECT tag_id, dstamp,
        ROW_NUMBER() OVER (PARTITION BY tag_id ORDER BY dstamp) AS rn
   FROM (SELECT tag_id, dstamp
           FROM inventory_transaction
          WHERE code IN ('Receipt', 'Stock Check')
         UNION
         SELECT tag_id, dstamp
           FROM inventory_transaction_archive
          WHERE code IN ('Receipt', 'Stock Check')) itl
)
SELECT I.location_id,
       I.sku_id,
       I.description,
       I.qty_on_hand,
       I.lock_status,
       I.tag_id,
       NVL(i.condition_id, ' ') Condition,
       TO_CHAR(FS.dstamp, 'DD/MM/YYYY') AS "First Seen"
  FROM inventory I
  LEFT JOIN fs 
    ON I.tag_id = fs.tag_id
 WHERE rn = 1

CodePudding user response：

ROWNUM = 1 is wrong condition because ROWNUM get only one row, well only one TAG_ID. try this SQL.

SELECT  I.location_id, I.sku_id, I.description, I.qty_on_hand, I.lock_status, I.tag_id, 
     nvl(i.condition_id,' ') Condition,
     to_char(FS.dstamp, 'DD/MM/YYYY') AS "First Seen"
     FROM inventory I
      LEFT JOIN 
               (SELECT itl.TAG_ID, MIN(dstamp) dstamp FROM
                 (SELECT tag_id, dstamp FROM inventory_transaction WHERE code IN ('Receipt','Stock 
                  Check')
                  union
                  SELECT tag_id, dstamp FROM inventory_transaction_archive WHERE code IN 
                  ('Receipt','Stock Check'))itl
                  GROUP BY itl.TAG_ID ) 
                  FS  
      ON I.TAG_ID = FS.TAG_ID