CODEWITHSUNDEEP
sqlsql-server
Trupti Pandit Samant
Trupti Pandit Samant

Reputation: 81

SQL How to find the Data which is not in table

I have this select statement, where I want to show found and not-found in the result set ... My query gives me only whatever (values) exists in the DB. How to also add not-found. Example:

5647994  1234 Data exist in table
5651061  8976 Data exist in table
5823683  null Data not exist in table
6115602  null Data not exist in table

SELECT *
FROM Carrier c
 WHERE (SUBSTRING(c.SrcFileName, 14, 7) in (
'5647994',
'5651061',
'5823683',
'6115602',
'6125795',
'6140114',
'6144781',
'6155133')

Upvotes: 3

Views: 915

Answers (2)

Kennah
Kennah

Reputation: 487

Updated based upon additional information.

If(OBJECT_ID('tempdb..#TempSrcFileName') Is Not Null)   Drop Table #TempSrcFileName

CREATE TABLE #TempSrcFileName
(
    src_file_name nchar(7)
)

INSERT INTO #TempSrcFileName (src_file_name)
VALUES
    ('5647994')
    , ('5651061')
    , ('5823683')
    , ('6115602')
    , ('6125795')
    , ('6140114')
    , ('6144781')
    , ('6155133')
;

SELECT
    t.src_file_name
    , 'Found' AS [Status]
FROM #TempSrcFileName t
    LEFT JOIN Carrier c ON SUBSTRING(c.SrcFileName, 14, 7) = t.src_file_name
WHERE   (SUBSTRING(c.SrcFileName, 14, 7) IS NOT NULL)
UNION SELECT
    t.src_file_name
    , 'Not Found' AS [Status]
FROM #TempSrcFileName t
    LEFT JOIN Carrier c ON SUBSTRING(c.SrcFileName, 14, 7) = t.src_file_name
WHERE   (SUBSTRING(c.SrcFileName, 14, 7) IS NULL)

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT t1.val,  
       IF (t2.id IS NULL, 'NOT FOUND', 'FOUND'),
       t2.*
FROM (
  SELECT '5647994' AS val UNION ALL SELECT '5651061' UNION ALL
  SELECT '5823683' UNION ALL SELECT '6115602' UNION ALL 
  SELECT '6125795' UNION ALL SELECT '6140114' UNION ALL
  SELECT '6144781' UNION ALL SELECT '6155133') AS t1
LEFT JOIN Carrier AS t2 ON t1.val = SUBSTRING(t2.SrcFileName, 14, 7)

The idea is to create an in-line table that contains all to-be-searched values. If we LEFT JOIN the original table to this in-line table, then all values are returned.

The above query assumes that id is a field of Carrier table. Checking this field for NULL/NOT NULL values identifies not found / found values respectively.

Upvotes: 3

Related Questions