SEO

#1 (**permalink**) 02-28-2008, 07:43 PM

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.

#2 (**permalink**) 02-28-2008, 07:43 PM

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" <soni29@hotmail.com> wrote in message
news:cad7a075.0401261757.41599596@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.

#3 (**permalink**) 02-28-2008, 07:43 PM

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" <soni29@hotmail.com> wrote in message
news:cad7a075.0401261757.41599596@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.

#4 (**permalink**) 02-28-2008, 07:43 PM

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
--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

please help with query