please help with query

[soni29]

hi,
i need some help with a query, also to find out if this is even
possible with sql. currently i have a table with the following data:

CustomerNumber CustomerBranch
123 NULL
123 1
123 2
221 NULL
221 5
555 1
555 9
125 NULL

now in this data, CustomerNumber and CustomerBranch are the keys, each
customer MUST have a CustomerBranch with null, those NULL
CustomerBranch's represent the company headquaters, while the ones
with numbers are the other offices. Occassionally data is missing, in
the example above CustomerNumber 555 does not have a NULL
CustomerBranch, this is wrong. Is there anyway in SQL to find all
those CustomerNumbers who do not have a null, there can only be one
null per CustomerNumber. i was thinking about using a
count(CustomerBranch) but not sure how to write it to count all those
CustomerBranchs per CustomerNumber that are equal to 0, if that's the
right way to do it.

Thank you.

[Oscar Santiesteban Jr.]

This might work, I would have to test it.

Select customernumber
from table
where customernumber not in ( select customernumber from table where
customernumber is NOT null)

This is untested....an not so efficient so don't do this on a 1 million row
database but for a small database is OKAY.
Oscar
"soni29" <so****@hotmail.com> wrote in message
news:ca**************************@posting.google.c om...

hi,
i need some help with a query, also to find out if this is even
possible with sql. currently i have a table with the following data:

CustomerNumber CustomerBranch
123 NULL
123 1
123 2
221 NULL
221 5
555 1
555 9
125 NULL

now in this data, CustomerNumber and CustomerBranch are the keys, each
customer MUST have a CustomerBranch with null, those NULL
CustomerBranch's represent the company headquaters, while the ones
with numbers are the other offices. Occassionally data is missing, in
the example above CustomerNumber 555 does not have a NULL
CustomerBranch, this is wrong. Is there anyway in SQL to find all
those CustomerNumbers who do not have a null, there can only be one
null per CustomerNumber. i was thinking about using a
count(CustomerBranch) but not sure how to write it to count all those
CustomerBranchs per CustomerNumber that are equal to 0, if that's the
right way to do it.

Thank you.

[Chuck Conover]

Soni,
Try this:

select customerNumber
from test
where not exists (select 1 from test t
where t.customerNumber = test.customerNumber
and t.customerBranch is null)
group by CustomerNumber
But it needs to do 2 table scans, which is pretty slow. If you'll do this
often, you should put an index on CustomerNumber.

You also might check out our SQL Server 2000 videos at
http://www.TechnicalVideos.net. They include lots of info on tuning and
performance as well as many other SQL Server 2000 topics. We also have
ASP.NET and Flash MX 2002 videos.

Hope this helps,
Chuck Conover
http://www.TechnicalVideos.net
"soni29" <so****@hotmail.com> wrote in message
news:ca**************************@posting.google.c om...

hi,
i need some help with a query, also to find out if this is even
possible with sql. currently i have a table with the following data:

CustomerNumber CustomerBranch
123 NULL
123 1
123 2
221 NULL
221 5
555 1
555 9
125 NULL

now in this data, CustomerNumber and CustomerBranch are the keys, each
customer MUST have a CustomerBranch with null, those NULL
CustomerBranch's represent the company headquaters, while the ones
with numbers are the other offices. Occassionally data is missing, in
the example above CustomerNumber 555 does not have a NULL
CustomerBranch, this is wrong. Is there anyway in SQL to find all
those CustomerNumbers who do not have a null, there can only be one
null per CustomerNumber. i was thinking about using a
count(CustomerBranch) but not sure how to write it to count all those
CustomerBranchs per CustomerNumber that are equal to 0, if that's the
right way to do it.

Thank you.

[David Portas]

SELECT customernumber
FROM Sometable
GROUP BY customernumber
HAVING COUNT(customerbranch)=COUNT(*)

CustomerNumber and CustomerBranch are the keys

Not so if Customerbranch is nullable. It helps if you include DDL with
questions like this so that it's clear what your keys and constraints are.

--
David Portas
SQL Server MVP
--