Very Computer

1. Getting accounts NOT in account list

Hello,

I am trying to figure out what I think should be simple sql statement
to retrieve rows, based on accounts, where there exists a shipto but
no billto.

A simple table with a single field, called account, which is char(8).
The account represents retailers, and there can be a chain of the same
retailer with multiple locations. The account is actually broken out
such that the first 5 chars are the account, and the last 3 represent
the specific retailer location.  The account with a 000 is the billto
location, while the 001 and greater are the shipto locations.

IE. 12345000     <== this is the billto account
    12345001     <== location 1
    12345002
    12345003

I need to get a list of the accounts where there is a shipto but which
don't have a billto ('000') in the table.

I thought I had it with ...

select *
from account_list
where substring(account,1,5) in (select distinct
substring(account,1,5) from account_list)
and substring(account,1,5) not in (select distinct
substring(account,1,5) from account_list where substring(account,6,3)
= '000')

Any tips would be appreciated.
Thanks,
Ric

2. Find Record where Len([Field]) is higher than Number

3. Changing service account for the Cluster andSQL service account

4. 17826 error

5. Local System Account or Domain Account For SQL Server Service

6. char vs. varchar

7. Update Account Balance from Account Transactions

8. US-NYC-Wall St.- DELPHI/SYBASE developer - consulting/full-time

9. confusion between local system account and this account in services log on as

10. Update Account Balance from Account Transactions

11. NT account VS Standard account...

12. changing the sqlserver account to a domain account

13. domain accounts vs. local accounts