Find Rows Using Nested Count, Join, or Having

Question

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

Shay Rojansky · Accepted Answer · 2011-03-03 14:13:21Z

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;

JumpingJezza · Accepted Answer · 2011-03-03 07:59:06Z

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

Andriy M · Accepted Answer · 2011-03-03 12:50:43Z

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

Collectives™ on Stack Overflow

Find Rows Using Nested Count, Join, or Having

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related