Forum Moderators: open
I get requests for info from our DB, pretty much selects that I throw into Excel Spreadsheets.
So I need to return about 10+ fields and one of the fields is the clients SSN along with name (reg00), Phone#, etc for each client.
I write the query out and it seems I cannot use the distinct function correctly. I try to make the focus the SSN and no matter what I try it keeps giving back dupe SSNs. I know it is thinking that as a whole, the row is distinct, but I need no duplicate SSNs.
The last one I tried went something like this...
select a.ssn0, a.acctnum, a.reg00, a.reg10, a.reg20, a.city0, a.address10, a.address20, a.zip0, a.state0, a.opendate
from accounts a
where a.acctnum in (select distinct accounts.ssn0 from accounts, sponaccount, accountreps
where sponaccount.acctnum = accounts.acctnum
and accountreps.acctnum = accounts.acctnum
and sponaccount.accttrail = accounts.accttrail
and sponaccount.sponcode not in ('X','Y')
and sponaccount.acctnum not in ('999999999','454545454','123456789')
and accounts.zip0 is not null
and accounts.regtype <>'X'
and accounts.opendate < '31-DEC-06')
Oh, and there is no ; because i use Zend.
So, any successful distincters out there?
[edited by: JebbyToast at 3:37 pm (utc) on July 23, 2007]
The major problem is that data entered in this DB is not always consistent. One operator will enter in #*$!X W. Circle Pl. while another operator will enter in #*$!X West Circle Pl.
thus making rows distinct from each other that shouldn't be.
I will keep plugging away at this.... thanks for response again.
select distinct a.ssn0, a.acctnum, a.reg00, a.reg10, a.reg20, a.city0, a.address10, a.address20, a.zip0, a.state0, a.opendate
from accounts, sponaccount, accountreps where sponaccount.acctnum = accounts.acctnum
and accountreps.acctnum = accounts.acctnum and sponaccount.accttrail = accounts.accttrail and sponaccount.sponcode not in ('X','Y') and sponaccount.acctnum not in ('999999999','454545454','123456789')
and accounts.zip0 is not null and accounts.regtype <>'X' and accounts.opendate < '31-DEC-06'
select distinct a.ssn0, a.acctnum, a.reg00, a.reg10, a.reg20, a.city0, a.address10, a.address20, a.zip0, a.state0, a.opendate
from accounts a, sponaccount, accountreps
where sponaccount.acctnum = a.acctnum
and accountreps.acctnum = a.acctnum
and sponaccount.accttrail = a.accttrail
and sponaccount.sponcode not in ('X','Y')
and sponaccount.acctnum not in ('999999999','454545454','123456789')
and accounts.zip0 is not null
and accounts.regtype <>'X'
and accounts.opendate < '31-DEC-06'
group by a.ssn0
