Selecting distinct records without nulls? 2

[Crofty616]

First of all, this is my first post, so hello! .

Second, I've got myself in a bit of a muddle trying to select some information from my tables, I'll try to explain the best way I can and simplify it as much as I can so you can see what I'm trying to do.

Basically, I have a field "code", and another with "date", there are multiple records for each code so something like:

Code Date

1222 2005-11-02
1222 NULL
1222 2007-01-24
1223 2006-04-17
1223 2008-05-21
1224 2009-01-11
1225 NULL

What I want to do is return all of the codes that don't have a NULL value at any point, so using the data above I would only see the codes 1223 and 1224, not 1222 or 1225.

My actual query has a lot more going on (and is taking data from 3 tables) but this is where I just cant get my head around it.

I hope that makes sense!

Cheers

 

[PWise]

Select distinct code
from table
left join (Select distinct Code
From Table
where date is null
)nulls
on nulls.code=table.code
where nulls.code is null

 

[jjefferson]

Alternatively:

select distinct code from table
where code not in
(select distinct code from table where date is null)

 

[Crofty616]

Cheers for the replies, jjefferson's method fitted what i needed perfectly.

Thanks again both!