CODEWITHSUNDEEP
sql-servert-sql
Hannah
Hannah

Reputation: 57

If ID is not in where list T-SQL

I have a list of ID's in my where clause and i want the query to return the ID's that are missing from this list

select distinct( b.PORTFOLIO_ID)
FROM [FIND].[dbo].[HOLDINGS_BREAKDOWN] b
where b.PORTFOLIO_ID in (91,
93,
94,
95,
2010912563)
and b.HLDG_DATE='2017-05-05'

How can i obtain the list of ID's that are not yet loaded into the Database?

Upvotes: 0

Views: 1359

Answers (4)

avb
avb

Reputation: 1753

I do it this way:

select v.PORTFOLIO_ID
from (values(91),(93),(94),(95),(2010912563))v(PORTFOLIO_ID)
    left outer join [FIND].[dbo].[HOLDINGS_BREAKDOWN] b on b.PORTFOLIO_ID = v.PORTFOLIO_ID and b.HLDG_DATE='2017-05-05'
where b.PORTFOLIO_ID is null

Upvotes: 2

TriV
TriV

Reputation: 5148

You could put your list Ids into a table and use NOT EXISTS like this

select * 
FROM (values (91),(93),(94),(95),(2010912563)) v(t)
WHERE NOT EXISTS(SELECT 1 FROM [FIND].[dbo].[HOLDINGS_BREAKDOWN] b 
                          WHERE b.PORTFOLIO_ID  = v.t 
                                and b.HLDG_DATE='2017-05-05' )

Upvotes: 1

xQbert
xQbert

Reputation: 35333

If the ID doesn't exist the system can't create/return the row as the data isn't present. So first we need to generate a table with your ID's and then outer join to the other table where no match is found (portfolio is null)

You can use a common table expression or you can use an inline view. This uses the common table expression C:

WITH C (ID) AS (SELECT 91 UNION 
                SELECT 93 UNION 
                SELECT 94 UNION 
                SELECT 95 UNION
                SELECT 2010912563) C

SELECT C.ID
FROM C
LEFT JOIN [FIND].[dbo].[HOLDINGS_BREAKDOWN] b
   on B.PORTFOLIO_ID = C.ID
  and B.HLDG_DATE=cast('2017-05-05' as date)
WHERE B.PORTFOLIO_ID is null

This uses the inline view:

SELECT C.ID
FROM (SELECT 91 UNION 
      SELECT 93 UNION 
      SELECT 94 UNION 
      SELECT 95 UNION
      SELECT 2010912563) C(ID)
LEFT JOIN [FIND].[dbo].[HOLDINGS_BREAKDOWN] b
   on B.PORTFOLIO_ID = C.ID
  and B.HLDG_DATE=cast('2017-05-05' as date)
WHERE B.PORTFOLIO_ID is null

Upvotes: 2

GandRalph
GandRalph

Reputation: 635

This will get you any IDs from your list that do not exist dbo.Holdings_Breakdown

create table #IDs(PORTFOLIO_ID int)
insert into #IDs(PORTFOLIO_ID) values(91),(93),(94),(95),(2010912563)

select a.PORTFOLIO_ID
from #ID a
left join [FIND].[dbo].[HOLDINGS_BREAKDOWN] b 
    on b.PORTFOLIO_ID = a.PORTFOLIO_ID
    and b.HLDG_DATE='2017-05-05'
where b.PORTFOLIO_ID is NULL

Upvotes: 0

Related Questions