Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Red face Unanswered: reference numbers whose status is not 2 (was "SQL Newbie looking for a little help")

    Hi All,

    Not exactly a SQL programmer by a long way and I'm having a problem with a query and would like a little advice if someone would be so kind.

    I have a table with various fields, of which I'm interested in two (RefNumber and Status).

    The RefNumber is not unique has it can be linked to multiple users at the same time. So the data I have looks like this (for the two fields in question).
    RefNumber / Status
    1234 / 1
    1234 / 1
    1234 / 2
    3333 / 1
    3333 / 1
    3333 / 1
    5555 / 2
    5555 / 1
    What I'm trying to get out of the database is distinct reference numbers whose status is not at 2.

    So the result I'm after int this case would be "3333".

    I've tried the following query, but it doesn't seem to work.

    Code:
    select ReferenceNumber from CountTable c where Status<
    (SELECT max(status) as Status
    FROM  CountTable cl
    where cl.ReferenceNumber = c.referencenumber 
    group by referencenumber)
    Any pointers greatly appreciated.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Your nested query just needs to find those refnumbers that have a status of 2, and your outer query finds the rest

    Code:
    SELECT DISTINCT REFNUMBER
    FROM COUNTTABLE
    WHERE NOT REFNUMBER IN (
    SELECT DISTINCT REFNUMBER
    FROM COUNTTABLE
    WHERE STATUS=2)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select ReferenceNumber 
      from CountTable 
    group
        by ReferenceNumber
    having sum(
           case when status = 2
                then 937
                else 0 end ) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Rudy,

    I'll concede that you're much better at this than I am, so I'll assume
    that your's is the better solution. My question is "Why"?

    Mark
    Inspiration Through Fermentation

  5. #5
    Join Date
    Oct 2004
    Posts
    7
    Thanks guys - both those queries work perfectly.

    I must admit, I have little more idea of what RedNeckGeek's is doing over r937's query.

    I'll have a read up on the unknown (to me) functions you use.

    Don't think I'll ever fully understand SQL, which is quite sad as it seems quite powerful.

    I'm sure, when I do things in code to manipulate data read from a DB, I could do them in SQL with much less overhead. At the moment, I used stored procedures in SQLServer to get data, manipulate it in code then update the DB again through stored procs.......I can almost hear your screams of pain from here!

    Any pointers to some good idiot-proof tutorials??

    Thanks again for your help.

    Regards,

    Mark J.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or still:
    Code:
    SELECT DISTINCT RefNumber
    FROM   CountTable AS c
    WHERE  NOT EXISTS (
                       SELECT 1
                       FROM   CountTable
                       WHERE  RefNumber = c.RefNumber AND Status = 2)
    This might be more performant than the others (depending on the size of the table, the average number of entries per RefNumber, and the RDBMS).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You actually seem to have a pretty good deal working already. You understand that SQL is powerful, but like any powerful tool it requires some basic knowledge of the tool and/or instruction before you can use it safely. You've got somebody managing your SQL Server that understands your limits and is providing you with stored procedures to do the "heavy lifting" on the SQL side. This is definitely not a bad place to start!

    I'm not sure what kind of resources you are looking for (web sites, classes, books, etc). I'll give you a couple of my suggestions, but feel free to ask more or different questions.

    First and foremost, asking questions here is likely to be a really good source of information for you. There are a bunch of crusty old pharts like me, and a few folks that actually present a professional image too. We all have opinions, and most of those opinions are pretty good, and lord knows if you ask a question you're pretty likely to get some good answers.

    Don't overlook the possibility of classes at a local university, trade school, or even an Adult Education class. The hands on nature and the interaction with people is really valuable to most people in the early stages of the learning process for almost anything. The quality varies all over the map, running from astounding (in a good sense) to astounding (as in a waste of time) depending on the class, instructor, and student... While this can definitely be a "mixed bag" and can go wrong, it usually goes very, VERY right.

    If you are looking for books, then I'd recommend SQL Queries for Mere Mortals or the Hitchhiker's Guide for someone using Microsoft SQL Server.

    There are literally thousands of other resources. Give me a nudge in the right direction and I bet I can suggest something!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •