CODEWITHSUNDEEP
sqlsql-serverselect
Brij
Brij

Reputation: 13129

SQL: Get values present and not present in table based on list of string values

I have a SQL Table which contains list of Cities in a Column. I have a separate list of cities and I want to find out the cities which are present and not present in the table, but are not present and present in that List respectively.

Example

CityId    CityName
1            ABC
2            DEF
3            GHI
4            JKL
5            MNO

Say the list is: 'ABC', 'DEF', 'MNO', 'PQR', 'STU'

So as output I want two lists:

Present in Table : 

'ABC' 
'DEF' 
'MNO'

Not Present in Table : 

'PQR'
'STU'

Upvotes: 0

Views: 6013

Answers (3)

Volodymyr
Volodymyr

Reputation: 1255

Present in table values:

SELECT v.valueName AS PresentInTable from (VALUES ('ABC'),('DEF'),('MNO'),('PQR'),('STU')) 
v(valueName) 
LEFT JOIN Cities c on c.CityName = v.valueName
WHERE c.CityId IS NOT NULL

NOT present in table:

SELECT v.valueName AS NotPresentInTable from (VALUES ('ABC'),('DEF'),('MNO'),('PQR'),('STU')) 
v(valueName) 
LEFT JOIN Cities c on c.CityName = v.valueName
WHERE c.CityId IS NULL

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460028

I presume that your list is a string/varchar, then you need a split-function first to get all parts:

1.) Then can use an INNER JOIN to find all which are in the table:

SELECT CityId,CityName
FROM Cities c
INNER JOIN dbo.Split('ABC,DEF,MNO,PQR,STU',',')x
ON c.CityName = x.Item;

2.) and NOT EXISTS to find all which are in the list but not in the table:

SELECT x.Item As CityName
FROM  dbo.Split('ABC,DEF,MNO,PQR,STU',',')x
WHERE NOT EXISTS 
(
  SELECT 1 
  FROM Cities c
  WHERE c.CityName = x.Item
)

DEMO

Result:

1)

CITYID  CITYNAME
  1       ABC
  2       DEF
  5       MNO

2)

CITYNAME
PQR
STU

Here's my split-function:

CREATE FUNCTION [dbo].[Split]
(
    @ItemList NVARCHAR(MAX), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(MAX)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END

Upvotes: 1

bwperrin
bwperrin

Reputation: 692

It looks like your "list" is in a table. This should be simply:

select distinct value from list where value in (select distinct city from cities)

and:

select value from list exclude select city from cities

Upvotes: 0

Related Questions