CODEWITHSUNDEEP
phpsql
George Newton
George Newton

Reputation: 3293

SQL: Selecting IDs that don't have any rows with a certain value for a column

I want to select the distinct IDs (which is associated with multiple rows) that DOESN'T have any rows where VAL = 'current'.

For example, in a table like this:

PK | ID | VAL 
-------------
 1 | 23 | deleted
 2 | 23 | deleted
 3 | 23 | deleted
 4 | 45 | current
 5 | 45 | deleted
 6 | 45 | deleted
...|................

I would want it to return ID 23, because it has no rows where VAL='current'. Note that in this table, the primary keys (PK) are unique, but IDs are not (hence the need to use DISTINCT or GROUP BY).

Here is what I have in PHP:

$conn = someConnect("");

// returns ids associated with the number of rows they have where VAL != 'current'
$sql = "SELECT id, count(*) FROM table WHERE val != 'current' GROUP BY id"

$stid = oci_parse($conn, $sql);
oci_execute($stid);

oci_fetch_all($stid, $arr, OCI_FETCHSTATEMENT_BY_ROW);

foreach ($arr as $elm) {
   $id = key($elm);
   $non_current_count = $elm[$id];

   // counts the number of rows associated with the id, which includes VAL = 'current' rows
   $sql2 = "SELECT count(*) FROM table WHERE id = $id";

   $stid2 = oci_parse($conn, $sql2);
   oci_execute($stid2);
   $total_count = oci_fetch_array...
   if ($total_count != $non_current_count) {
      $output[] = $id;
   } 
   ...
}

oci_close($conn);

That's the general gist of it. As you can see, it takes two SQL statements to accomplish this task. Is there a shorter way of doing this?

Upvotes: 7

Views: 12617

Answers (3)

Manik
Manik

Reputation: 1

If you want to use Having Clause then use like this

SELECT
    id,
    count(*) as Total
FROM Status S1
WHERE val <> 'current'
GROUP BY id
HAVING count(*) = (SELECT COUNT(*) FROM Status S2 WHERE S2.id = S1.id)

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can use having

SELECT
    id,
    count(*) as Total
FROM table
WHERE val <> 'current'
HAVING Total > 0

Output

| ID | TOTAL |
|----|-------|
| 23 |     5 |

Fiddle

Upvotes: 1

Barmar
Barmar

Reputation: 780713

SELECT DISTINCT id
FROM table
WHERE id NOT IN (SELECT id
                 FROM table
                 WHERE val = 'current')

or:

SELECT a.id
FROM table a
LEFT JOIN table b ON a.id = b.id AND b.val = 'current'
WHERE b.id IS NULL

Upvotes: 17

Related Questions