CODEWITHSUNDEEP
sql-server
hrishi
hrishi

Reputation: 1559

duplicate in sql server

select a.AccountNumber,w.BillingAdmin
from dbo.ACCTHIST a left join dbo.WTABLE w on a.BillingClassKey = w.TablDKey
where a.AccountNumber in ('0000001779     W',
  '0000001779     W',
  '0000001779     W',
  '0000001779     W',
  '0000005502     W',
  '0000005502     W',
  '0000005502     W',
  '0000005502     W')

Result is:

  Account Number                    Billingadmin
  0000001779     W                  VB-Rajendrasingh R                                          
  0000005502     W                  NULL
  0000005502     W                  VB-Rajendrasingh R     
  0000005502     W                  VB-Rajendrasingh R

The question is I am puting thrice account number '0000001779 W', and thrice '0000005502 W'. Still I am getting One result for 0000001779 W and three row for 0000005502 W
Sorry I cant view the page in proper format ,so its difficult to put question in good format

Upvotes: 0

Views: 61

Answers (2)

gbn
gbn

Reputation: 432657

Of course: you have 3 rows in WTABLE for 0000005502 W

The IN clause ignore duplicates too so it is effectively 

... in ('0000001779     W',  '0000005502     W')

Upvotes: 3

Matt
Matt

Reputation: 4127

You only need one instance of each of your criteria in order to get all matching values anyway, try this.

select a.AccountNumber,w.BillingAdmin
from dbo.ACCTHIST a left join dbo.WTABLE w on a.BillingClassKey = w.TablDKey
where a.AccountNumber in ('0000001779     W',
  '0000005502     W')

you should get the same results.

Are you sure you have that value more than once in the data? To check try this:

SELECT COUNT(AccountNumber) From dbo.ACCTHIST WHERE AccountNumber = '0000001779     W'

Upvotes: 0

Related Questions