CODEWITHSUNDEEP
phpmysqlsql
zozo
zozo

Reputation: 8612

How to check if a row does not exist in a database

I have the following setup:

The thing is that not all records are there. For example row 1 2 i1 might be missing.

What I would like to do, from query (without getting all rows and iterate through them) is to see IF any row is missing (I don't care witch one, only IF one is missing).

This is a very simplified example for a much more complex problem so if I didn't expose it clear, or I forgot to mention anything feel free to ask for details.

A select and process in PHP is acceptable, as long as I don't select everything in table (although I don't see how to do this by processing data without selecting all but felt like it worth mentioning).

Some of you asked for a pattern so...:

Let's simplify some more... let's say column one has an array of possible data that can be found there, same for column 2, and already said it for column 3. All possible combinations between the 3 of then should be found on the table. I need to know if any are missing...

Upvotes: 1

Views: 1503

Answers (5)

Kickstart
Kickstart

Reputation: 21533

I would think about doing it this way, which will still work even if there are duplicates in your list of values. This sames doing any looping over the resulting fields (just a single row comes back which will tell you how many unique in your array are not found on the table.

SELECT COUNT(*)
FROM (SELECT 'i1' AS aCol
UNION
SELECT 'i2' AS aCol
UNION
SELECT 'i3' AS aCol
UNION
.......
UNION
SELECT 'in' AS aCol) Sub1
LEFT OUTER JOIN aTable
ON  Sub1.aCol = aTable.c
WHERE aTable.c IS NULL

Could also be modified very easily to bring back a list of the items that are not found should that be required in the future.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can do this with a simple count. The number of expected rows is the number of distinct elements in A times the number of distinct elements in B times the number of distinct elements in C.

To count the number that are missing, just do arithmetic on the appropriate values:

select (cnt - cntA*cntB*cntC) as NumMissingRows
from (select count(distinct a) as cntA,
             count(distinct b) as cntB,
             count(distinct c) as cntC,
             count(*) as cnt
      from t
     ) t

Upvotes: 1

lz.
lz.

Reputation: 331

Assume we have a table with this data.

mysql> SELECT * FROM stuff;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 | i1   |
|    1 |    1 | i2   |
|    1 |    2 | i2   |
|    1 |    2 | i3   |
|    2 |    1 | i1   |
+------+------+------+
5 rows in set (0.00 sec)

Lets also assume that all possible values for C is in the table. Then we can construct a reference table like this.

mysql> SELECT a,b,c FROM (SELECT DISTINCT a,b FROM stuff) t1 CROSS JOIN (SELECT DISTINCT c FROM stuff) t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 | i1   |
|    1 |    2 | i1   |
|    2 |    1 | i1   |
|    1 |    1 | i2   |
|    1 |    2 | i2   |
|    2 |    1 | i2   |
|    1 |    1 | i3   |
|    1 |    2 | i3   |
|    2 |    1 | i3   |
+------+------+------+
9 rows in set (0.00 sec)

We can then compare the table with actual data and the reference table by joining them together like this and get all missing rows like this:

mysql> SELECT * FROM stuff RIGHT JOIN (SELECT a,b,c FROM (SELECT DISTINCT a,b FROM stuff) t1 CROSS JOIN (SELECT DISTINCT c FROM stuff) t2) r ON stuff.a = r.a AND stuff.b = r.b AND stuff.c = r.c WHERE stuff.a IS NULL;
+------+------+------+------+------+------+
| a    | b    | c    | a    | b    | c    |
+------+------+------+------+------+------+
| NULL | NULL | NULL |    1 |    2 | i1   |
| NULL | NULL | NULL |    2 |    1 | i2   |
| NULL | NULL | NULL |    1 |    1 | i3   |
| NULL | NULL | NULL |    2 |    1 | i3   |
+------+------+------+------+------+------+
4 rows in set (0.00 sec)

The RIGHT JOIN ON a,b,c will match the rows in the reference table r against the actual rows. The missing rows will manifest as NULL on stuff side. Therefore we can get all missing rows by selecting any row with a NULL field in the stuff table.

Edit: You can change the SELECT * ... in the last query to SELECT count(*) ... and you get the number of missing rows in this case 4.

Upvotes: 1

Friederike
Friederike

Reputation: 1282

assuming you know he values for column a and b you could try the following:

select c, count (*) group by c;

this would tell you how many entries for each value are there.

i1  3
i2  0
in  3

then you could iterate over that result to see whats missing

Upvotes: 1

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31743

What about such a query, this might not be the best performance but for a one time task this should work.

SELECT t1.id,
(
  SELECT t2.id FROM table t2 WHERE t2.id < t1.id ORDER BY t2.id DESC LIMIT 1
) as prv
FROM table t1
HAVING id <> prv + 1

Upvotes: 0

Related Questions