views:

39

answers:

2

Ok here goes:

I have a table with id ( can be duplicate but not NULL ) and value ( can be duplicate and NULL )

id      value
-----   -----
1       red
1       red
1       (null)
2       blue
2       blue
3       (null)

So how do I return the id's and value's of all the records that have a value, but if a null value is also found don't include it in the result set.

So the return would be

id      value
-----   -----
2       blue

as id 1 and 3 have a value of (null) in one or more of the results

+2  A: 

It's a typical "select where not exists"-type query with many ways to write the answer, for example:

Using a LEFT JOIN / WHERE ... IS NULL:

SELECT DISTINCT T1.id, T1.value
FROM your_table T1
LEFT JOIN your_table T2
ON T1.id = T2.id AND T2.value IS NULL
WHERE T2.id IS NULL

Using NOT IN:

SELECT DISTINCT id, value
FROM your_table
WHERE id NOT IN
(
    SELECT DISTINCT id
    FROM your_table
    WHERE value IS NULL
)

Using NOT EXISTS:

SELECT DISTINCT id, value
FROM your_table
WHERE NOT EXISTS
(
    SELECT NULL
    FROM your_table T1
    WHERE your_table.id = T1.id AND T1.value IS NULL
)
Mark Byers
Don't you need distinct id, value on your first select, otherwise won't you get "2 blue" back twice?
dcp
@dcp: Yes, fixed thanks - though his question state 'eliminate multiple values if same identifier has an associated NULL value' yet '2 blue' does NOT have an associated NULL value so I'm not entirely sure why these duplicates should be removed.
Mark Byers
Thanks I was doing it backwards, in my sub query I was using the NOT in and the main query was where value = null. Thanks
Phill Pafford
@Mark Byers - I was going by his expected output, in which only one 2 blue was returned.
dcp
+1  A: 
select t1.id, t1.value
from MyTable t1
left outer join MyTable t2 on t1.id = t2.id and t2.value is null
where t2.id is null
group by t1.id, t1.value
RedFilter