Where is my logic flawed.

[FatalExceptionError]

I basically have a table that has multiple columns to hold data for transactions but it really boils down to two columns. A license number(char(48)) and a status code(char(2)). A license number goes through multiple status codes as it flows through the system with a new line for each. What I am trying to get is lines that have been received but not withdrawn. The code for what I am doing is below. I get an empty set and I don't understand why. If I query seperately I get rowcounts with differences as expected (recieved > withdrawn).

select 
	license
from 
	transactions 
where 
		status = 'Received'
and 
 license not in (select license from transactions where status= 'Withdrawn')
order by license

Where is my logic wrong or is there a better way to do this?

P.S. This is a SQL2K DB

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[LNBruno]

First guess:

status code(char(2))

doesn't support

status = 'Received'

or

status= 'Withdrawn'

< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLDenis]

you have a NULL value in the transactions table
add where license is not null or use NOT EXISTS or a LEFT JOIN

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

You have at least ONE license with status 'Withdrawn'
To be more specific can you post some simple data and what you want from it?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[FatalExceptionError]

LNBruno the status is actually a number in a char field, I just changed it to something easier to read.

SQLDenis

Adding the not null clause worked but not exists doesn't. From my understanding of BOL exists returns null results and IN bypasses them. Is this wrong?

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[ESquared]

Use a LEFT JOIN.

The other methods are, in my opinion, not as good.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[FatalExceptionError]

What makes the left join better? Performance?

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't

 

[ESquared]

IN () is syntactically equivalent to a bunch of OR statements. The query optimizer might be smart enough to avoid the actual OR clause in a simple case like this and perform a JOIN anyway, but nonetheless what you have going on is a set operation.

WHERE NOT EXISTS(), depending on how it's written, can end up being a correlated subquery, completely the wrong thing to do performance-wise.

There may in fact be a performance benefit for using one way over another (and you should do testing to learn this). Here's how to use the left join.

It does look complicated and it can be confusing to know where to put the "W.status = 'withdrawn'" part, but a little examination and practice should make it clear what's going on.

select T.license
from
   transactions T
   left join transactions W ON T.license = W.license AND W.status = 'Withdrawn'
where
   T.status = 'Received'
   AND W.license IS NULL
order by T.license

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[FatalExceptionError]

Thanks
I was confused about "use a join" but I got it came to me in less than 3 seconds when I saw the code.

Performance wise the join appears to be faster as it will return a smaller amount of rows and the execution plan has far fewer tasks to complete. Both will compute the right amount for transactions due to the extra rows being adjustments.

=======================================
The statement below is true
The statement above is false

There are 10 kinds of people, those who know binary and those who don't