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.
| Author |
Topic |
|
divan
Posting Yak Master
153 Posts |
 Posted - 2012-09-13 : 09:11:33
|
| I have two table one is CLIENT TABLE that has CLIENT_NUMBER as the key and then I have another table called POLICY that also has a CLIENT_NUMBER as a key..There are more rows in the CLIENT_TABLE than in the POLICY table.. I need to list all the CLIENT_NUMBERS that are not in the POLICY_NUMBER.I wrote the following script SELECT CLIENT_NUMBERFROM MPL_EXPOSURE WHERE CLIENT_NUMBER NOT IN (SELECT DISTINCT CLIENT_NUMBER FROM CLIENT)the result is only 227 rowsbut when I do the select statement statement separately for each table and then compare there is a difference of approx 5000 rows... |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-09-13 : 09:27:48
|
| Hi divan, As per my understanding, you have to exclude client_numbers that are in POLICY table.SELECT CLIENT_NUMBER FROM CLIENTWHERE CLIENT_NUMBER NOT IN (SELECT DISTINCT CLIENT_NUMBER FROM POLICY)--Chandu |
 |
|
|
dmills99
Starting Member
7 Posts |
Posted - 2012-09-13 : 09:29:05
|
| your statement is very confusing - you say you have a client table which i can only assume is mpl_exposure and this has client_number in it? Also you have a table called policy that also has client_number in it... Then you state Client_Table (is this another table or just a field or are you referring to mpl_exposure?)you want to list all client_numbers in the client table? that aren't in the Policy_number? (another table, field?) please clarify...did you try reversing SELECT CLIENT_NUMBERFROM clientWHERE CLIENT_NUMBER NOT IN(SELECT DISTINCT CLIENT_NUMBER FROM mpl_exposure)sorry... can only guess at what you need... |
|
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2012-09-13 : 09:37:18
|
| Bandi your understanding is correct and I tried your suggestion and I get zero rows. dmills99 sorry for the confusion here is what I meant..I have a table called CLIENT and in this table there is a field called CLIENT_NUMBER.. I also have another table called MPL_EXPOSURE which also has a field called CLIENT_NUMBER. Now the number of rows(client_numbers) in the CLIENT table is greater than then the number of client_number in the MPL_EXPOSURE table... What I need is to find out which numbers in the CLIENT TABLE are not in the MPL_EXPOSURE table.. And yes I did try the refverse and I got zero rows... |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-09-13 : 09:45:42
|
| Hi divan,you are getting zero records means you may have NULL value for CLIENT_NUMBER of POLICY table....So, change WHERE condition as followsWHERE CLIENT_NUMBER NOT IN (SELECT DISTINCT CLIENT_NUMBERFROM POLICY WHERE CLIENT_NUMBER Is Not NULL)--Chandu |
|
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2012-09-13 : 10:02:48
|
| Thanks Bandi that worked... |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-09-14 : 02:16:36
|
| Welcome divan--Chandu |
|
|
|
|
|
|
SELECT