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

mjohnson3091 · #1 (**permalink**) 01-19-07, 05:56

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

Quote:

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.

RedNeckGeek · #2 (**permalink**) 01-19-07, 07:20

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)

r937 · #3 (**permalink**) 01-19-07, 08:01

Code:

select ReferenceNumber 
  from CountTable 
group
    by ReferenceNumber
having sum(
       case when status = 2
            then 937
            else 0 end ) = 0

RedNeckGeek · #4 (**permalink**) 01-19-07, 08:11

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

mjohnson3091 · #5 (**permalink**) 01-19-07, 08:21

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.

Peter.Vanroose · #6 (**permalink**) 01-20-07, 15:18

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

Pat Phelan · #7 (**permalink**) 01-21-07, 11:02

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

Internet Services

Web Development

Digital Marketing

Consumer Tech