SELECT DISTINCT - 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

General SQL Server Forums

New to SQL Server Programming

SELECT DISTINCT

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-09-19 : 14:14:20

I have a table called CLIENT with a key called CLIENT_NUMBER . I have another file called PHX_NOVA_CONTACT_INFO_TABLE where the key is EDI_SOURCE_PRIMARY_KEY_VALUE . then I have another table called MPL_EXPOSURE that also has a key called CLIENT_NUMBER..

I am trying to do the following:

1) List the client number that are in the CLIENT table but not in PHX_NOVA_CONTACT_INFO_TABLE.
2) Then take that result and list the CLIENT_NUMBERS that are not in MPL_EXPOSURE..

The first select I get the right answer but then when I do the second select I get zero and I really should have a large number..

here is the script I uses

DROP TABLE #TEMP1
---DROP TABLE #TEMP2
SELECT C.CLIENT_NUMBER, C.FNAME1, C.LNAME1, C.SUFFIX1,C.INIT1
INTO #TEMP1
FROM CLIENT C
WHERE C.CLIENT_NUMBER NOT IN (SELECT DISTINCT EDI_SOURCE_PRIMARY_KEY_VALUE FROM PHX_NOVA_CONTACT_INFO_TABLE)

SELECT DISTINCT T.CLIENT_NUMBER, T.FNAME1, T.LNAME1, T.SUFFIX1,T.INIT1
FROM #TEMP1 T
WHERE T.CLIENT_NUMBER NOT IN (SELECT CLIENT_NUMBER FROM MPL_EXPOSURE)

Any ideas???

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-19 : 14:44:05

Check if you really have a column named CLIENT_NUMBER in the MPL_EXPOSURE table. If there is not (perhaps it is spelled differently, this can happen).

Also, you don't need the intermediate temp table unless you are planning to do something with it later. You can combine the two into one query like this (note, I have qualified the inner query columns with table aliases):

SELECT DISTINCT C.CLIENT_NUMBER,
       C.FNAME1,
       C.LNAME1,
       C.SUFFIX1,
       C.INIT1
FROM   CLIENT C
WHERE C.CLIENT_NUMBER NOT IN (SELECT DISTINCT p.EDI_SOURCE_PRIMARY_KEY_VALUE
                               FROM   PHX_NOVA_CONTACT_INFO_TABLE p) 	
	AND CLIENT_NUMBER NOT IN (SELECT m.CLIENT_NUMBER
                               FROM   MPL_EXPOSURE m)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources