please help with query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • soni29
    #1

    please help with query

    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(CustomerB ranch) 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.
    Tags: None
    • Oscar Santiesteban Jr.
      #2
      Re: please help with query

      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.0 401261757.41599 596@posting.goo gle.com...[color=blue]
      > 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(CustomerB ranch) 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.[/color]


        Comment

        • Chuck Conover
          #3
          Re: please help with query

          Soni,
          Try this:

          select customerNumber
          from test
          where not exists (select 1 from test t
          where t.customerNumbe r = test.customerNu mber
          and t.customerBranc h 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.0 401261757.41599 596@posting.goo gle.com...[color=blue]
          > 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(CustomerB ranch) 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.[/color]


            Comment

            • David Portas
              #4
              Re: please help with query

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

              [color=blue]
              > CustomerNumber and CustomerBranch are the keys[/color]

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


                Comment

                Previous template Next
                Working...