Find Rows Using Nested Count, Join, or Having

I have data in a table that might look like so:

    id | streetnum | name | item
    -----------------------------
    1  | 100       | a    | 0
    2  | 100       | b    | NULL
    3  | 100       | c    | NULL
    4  | 101       | d    | NULL
    5  | 101       | e    | NULL
    6  | 102       | f    | 1

I'm trying to put together a query which would identify the identical streenum's where the item column 开发者_如何学Gohas both a value and one or more NULL's. In the example above, the query's result should be:

100

My first instinct is to put together a nested query involving count(*) but I want to see what other ideas you guys come up with.

---

Also possible with a self join:

SELECT DISTINCT streetnum FROM atable AS a1,atable AS a2 WHERE a1.streetnum=a2.streenum AND a1.item IS NULL AND a2.item IS NOT NULL;

---

Here is a query that works in SQLServer. I haven't tested the syntax for mysql.

SELECT streetnum FROM YourTable
WHERE streetnum IN
 (SELECT streetnum FROM YourTable
  WHERE item IS NULL 
  GROUP BY streetnum) 
AND streetnum IN
 (SELECT streetnum FROM YourTable
  WHERE item IS NOT NULL 
  GROUP BY streetnum)
GROUP BY streetnum

---

SELECT streetnum
FROM atable
GROUP BY streetnum
HAVING MAX(item) IS NOT NULL
  AND COUNT(CASE WHEN item IS NULL THEN 1 END) > 0

MAX(item) can be replaced by MIN(item) or SUM or AVG. Also this part of condition can be replaced by COUNT(item) > 0.

The more tricky part is where you must account for the presence of NULLs as well. Here you'll have to use CASE, because you need to turn the NULL into a value to be able to use it in an aggregate. Once it is a value, you can COUNT or SUM it (MAX, MIN etc. would do as well).