CODEWITHSUNDEEP
mysqlselect
Jhonatan Sandoval
Jhonatan Sandoval

Reputation: 1293

MySQL - how to select query values which doesn't have a specific data

I'm having some troubles (thinking about it) doing a select from this table.

tb_details:

id            status             det_id
1               5                  22  
2               1                  22
3               0                  22
4               5                  25
5               1                  25
6               5                  27
7               1                  27
8               5                  32
9               1                  32
10              0                  32

How can i make a select query to show just the det_id values which doesn't have a 0 in the table, maybe something like this:

det_id
  25
  27

Upvotes: 2

Views: 2271

Answers (3)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Another and simpler way of doing this is:-

SELECT DISTINCT det_id FROM TB
WHERE det_id NOT IN
(SELECT det_id RFOM TB WHERE status = 0);

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

One approach (out of several workable approaches) is to use an anti-join pattern.

SELECT t.det_id
  FROM this_table t
  LEFT
  JOIN ( SELECT r.det_id
           FROM this_table r
          WHERE r.status = 0
          GROUP BY r.det_id
       ) s
    ON s.det_id = t.det_id
 WHERE s.det_id IS NULL
 GROUP BY t.det_id

Let's unpack that a bit.

The inline view (aliased as s) returns a distinct list of det_id values for which a status=0 row does exist in this_table.

The LEFT JOIN operation returns all det_id values from this_table t, along with the matching det_id from s. If a match is not found, the "left outerness" of the join means that all rows from t will be returned, whether a match is found or not.

The "trick" is the predicate in the WHERE clause, testing whether the value of the column returned from s is NULL or not. The predicate effectively excludes any rows from t which had a matching row found in s.

So, all that remains to return is rows from t that didn't have a match in s.

We add a GROUP BY t.det_id (or we could add the DISTINCT keyword), to return a list of distinct det_id values.

This isn't the only approach. You could also use a NOT EXISTS predicate ...

SELECT t.det_id
  FROM this_table t
 WHERE NOT EXISTS
       ( SELECT 1
           FROM this_table r
          WHERE r.det_id = t.det_id
            AND r.status = 0
       )
 GROUP BY t.det_id

(This differs slightly, in that a row with a NULL value for det_id could be returned, where the previous query would not return it. That first query could be tweaked to make it return the same result as this.)

You could also use a NOT IN, taking care that the subquery does not return any NULL values for det_id.

SELECT t.det_id
  FROM this_table t
 WHERE t.det_id NOT IN
       ( SELECT r.det_id
           FROM this_table r
          WHERE r.status = 0
            AND r.det_id IS NOT NULL
          GROUP BY r.det_id
       )
 GROUP BY t.det_id

There are several statements what will return the specified resultset.

Upvotes: 4

Perocat
Perocat

Reputation: 1521

If you want to only have the det_id where no other colum is 0 you should write

SELECT det_id FROM TABLE WHERE
 Col1 <> 0
AND
 Col2 <> 0

and so on...

If you want only 1 result per type add

GROUP BY det_id

at the end of the query.

Upvotes: -1

Related Questions