I'm lost, please help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

I'm lost, please help

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2008-07-01 : 16:54:17

I have the following:

select distinct ID_CUSIP 
into #yadda 
from BBCusips 
go 
 
create unique index yadda_index on #yadda (ID_CUSIP) 
go 
 
 
use imcwharehouse 
select distinct(a.cusip) from 
( 
select mh.Accountid,mh.cusip,ms.SecType,ms.SECTOR,ms.INDS_Code from  MarshallFundsHoldings mh 
left join Metavante_SecurityMaster ms on mh.cusip = ms.cusip
    where mh.cusip not in 
    ( 
        --select ID_CUSIP from BBCusips 
        select ID_CUSIP from #yadda 
    ) 
) as a 
go 

drop table #yadda

What I am trying to do is to identify the unique cusips that i get from :

select distinct(a.cusip) from 
( 
select mh.Accountid,mh.cusip,ms.SecType,ms.SECTOR,ms.INDS_Code from  MarshallFundsHoldings mh 
left join Metavante_SecurityMaster ms on mh.cusip = ms.cusip
    where mh.cusip not in 
    ( 
        --select ID_CUSIP from BBCusips 
        select ID_CUSIP from #yadda 
    )

and find out which of those cusips I dont have in

select ID_CUSIP from #yadda

The problem is I know that I have CUSIPS that are not in, but my results set keeps coming back with 0 records

When i change the query to look @ cusips that are in instead of not in, it brings back 880 records when I have 950 records total in the table. I just don't understand why it doesn't bring back any results when I am looking for CUSIP (NOT IN). Any help would be greatly appreciated, sorry if this is confusing.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2008-07-01 : 17:17:46

I believe FREAKING nulls were screwing me, after reading this:

http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!777.entry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 00:10:01

[code]select distinct(a.cusip) from
(
select mh.Accountid,mh.cusip,ms.SecType,ms.SECTOR,ms.INDS_Code from MarshallFundsHoldings mh
left join Metavante_SecurityMaster ms on mh.cusip = ms.cusip
left join (
--select ID_CUSIP from BBCusips
select ID_CUSIP from #yadda
) t
on t.ID_CUSIP =mh.cusip
where t.ID_CUSIP is null)as a[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources