Return Id's That Are Not in the Database - MYSQL

Question

Return Id's That Are Not in the Database - MYSQL

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

1

Problem

In a table X and I have N records, however these records are not sequential (throughout insert ), Id will always be greater than previous, but not necessarily the number following the previous one.)

I need to know what id's do not have in the bank.

Example

SELECT Id FROM X

Return

I need to return the id's 3,5,6,7,9,10,11 that are missing.

I searched a lot, but only found results in English and these results unfortunately did not solve the problem.

Question

How to create a query that can solve this in just one column and without creating other tables?

mysql sql query

asked by anonymous 10.08.2017 / 16:23

3 answers

Separate column in multiple lines (split) Correlating values in Python Arrays

score 1 · Answer 1

Try this solution: I played all the ID that exist in a temporary table with the status of "exist"; then with a counter, check who does not exist and adds it to the status of "does not exist."

DROP TEMPORARY TABLE IF EXISTS tmpIds;
CREATE TEMPORARY TABLE tmpIds SELECT Id, 'existe' as 'Status' FROM X

DECLARE maior_id INT DEFAULT 0;
DECLARE contador INT DEFAULT 1;
DECLARE existe varchar(3);

SELECT maior_id = MAX(Id) FROM X

WHILE contador <= maior_id DO
    IF NOT EXISTS (SELECT 1 FROM tmpIds WHERE Id = contador)
        INSERT INTO tmpIds(Id, Status) VALUES(contador, 'nao existe');

    SET contador = contador +1;
END WHILE;

SELECT * FROM tmpIds

score 1 · Answer 2

Try to use this query, if you want to read more here you have link

SELECT a.id + 1 AS start, MIN(b.id) - 1 AS end
    FROM minha_tabela AS a, minha_tabela  AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id)

E It will return the missing records in sequential order

Note id is all tiny, see if your id is also everything tiny

score 0 · Answer 3

Here is a solution in a query:

SELECT
    y.id
FROM
    (
        SELECT
            @row := @row + 1 as id
        FROM 
            (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
            (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
            (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
            (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
            (SELECT @row:=0) AS y
    ) AS y
    LEFT JOIN (SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 4 AS id UNION SELECT 5 AS id UNION SELECT 12 AS id) AS x ON y.id = x.id
WHERE
    x.id IS NULL
    AND y.id < (SELECT MAX(id) FROM (SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 4 AS id UNION SELECT 5 AS id UNION SELECT 12 AS id) AS x)

You only need to replace the joins (SELECT 1 AS id UNION SELECT 2 AS id UNION SELECT 4 AS id UNION SELECT 5 AS id UNION SELECT 12 AS id) from your database table. And this solution would have a limitation of up to 10,000 records.