Search code examples
sqlselectsubquery

SELECT items that were recently change

I want to select Item numbers that were recently changed based on timestamp, THEN use those item numbers to select/find ones that have bin=MASTER. Below what I tried:

SELECT  (SELECT cItemno FROM TB_BinLoc WHERE Updates > {ts '2020-12-17 05:00:00'} ) as x , cBin
FROM TB_BinLoc y
WHERE y.cItemno = x AND cBin = 'MASTER'

=============== This is what I ended up with:

SELECT cItemno, cWarehouse, cBin, nCounted, Updates FROM TB_BinLoc WHERE Updates > {ts '2020-12-17 05:00:00'}
UNION
SELECT DISTINCT y.cItemno, y.cWarehouse, y.cBin, y.nCounted, y.Updates
FROM TB_BinLoc y
JOIN TB_BinLoc x on y.cItemno = x.cItemno
Where y.cBin = 'MASTER' AND y.cWarehouse = 'MAIN'
AND x.Updates > {ts '2020-12-17 05:00:00'}
ORDER BY cBin ASC
Solution

  • Use join as follows:

    SELECT distinct x.cItemno as x , y.cBin
  FROM TB_BinLoc y
  Join TB_BinLoc x on y.cItemno = x.cItemno
 Where y.cBin = 'MASTER'
   And x.Updates > {ts '2020-12-17 05:00:00'}