Help with SQL statement to show in my SELECT that data does not exist

Question

Help with SQL statement to show in my SELECT that data does not exist

Navigation

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

2

Greetings guys I need help with the following:

I have a table with more data than 6000 data and I need to make a query in phpmyadmin where I will provide a data packet to look for and this will tell me what data I am looking for do not exist.

What I Have
With the following structure gives me as a result of the block of data supplied those that are example if I am looking for 200 data I realize that there is already data that exists because it shows me as a result a total of 180 that is there are 20 that do not exist, and I require determine which of the supplied serials are those that do not exist and I do not know how to generate the SQL statement for it.

SELECT serial FROM tarjetas WHERE serial IN ('a','b','c','d','e','f','g','h'......)

Also try

SELECT serial, ISNULL(serial,'NO EXISTE') AS No_Existe.....

But I get an error.

It is worth mentioning that among the data that I am consulting many of them are already in the table, and I need to know which ones do not are in the table.

An Example of what I require

If I have the data:
Serial contenido a aa b aa c aa x aa y aa z aa
And my query is to find the serials ('c','d','e','f') enlisted that the serials d, e y f are those that do not exist because the serial c if it exists in my table.

mysql sql phpmyadmin

asked by Jose M Herrera V 19.11.2018 в 15:30

source

3 answers

Run a file error in Async and Await

score 0 · Answer 1

0

You can use your same query but changing the " IN " to the " NOT IN ". For example:

SELECT serial FROM tarjetas WHERE serial NOT IN (a,b,c,d,e,f,g,h)

answered by 19.11.2018 в 15:55

score 0 · Answer 2

I do not know if it's the most correct way but I think this would be worth it.

(1) First we get those that are with this query (up to here all normal)

SELECT DISTINCT serial FROM tarjetas WHERE serial IN ('a','b','c','d','e','f','g','h')

(2) Now we create a select with all the values

SELECT valor WHERE valor IN ('a','b','c','d','e','f','g','h')

Now we build the final query. Basically what we do is tell him to give me all the values of the query (2) that are not in the (1)

SELECT * FROM (SELECT valor WHERE valor IN ('a','b','c','d','e','f','g','h')) TABLA_TEMP WHERE TABLA_TEMP.valor NOT IN (SELECT DISTINCT serial FROM tarjetas WHERE serial IN ('a','b','c','d','e','f','g','h')).

It is quite probable that it can be improved but it does not occur to me now.

score 0 · Answer 3

If your package (serials to filter) you get them with the IN and if you only need those that are not in that set, you should only use the reserved word NOT next to IN , as follows:

SELECT serial FROM tarjetas WHERE serial NOT IN ('a','b','c','d','e','f','g','h', ...)

EDITING

Due to the clarification, this could help you.

-- Creas variable tabla
-- Creamos una variable tipo tabla para poder filtrar mediante join contra la tabla en base.
DECLARE @SERIALS TABLE(
    id_serial CHAR(20)
)

-- Insertas los datos
-- En esta parte cargas los datos que tienes en tu array, a la variable tipo tabla.
INSERT INTO @SERIALS
SELECT 'a';
INSERT INTO @SERIALS
SELECT 'b';

-- Reviso que la variable tabla esté cargada
SELECT*
FROM @SERIALS

-- Filtro los datos que no existen en la tabla de Tarjetas
SELECT*
FROM @SERIALS AS B
LEFT JOIN TARJETAS AS S  --  Join contra la tabla en base.
    ON  (B.id_serial = S.serial)
WHERE S.serial IS NULL; -- Condición.

I hope it helps you. Anyway, I ask the forum for help in the part of data loading in the variable type table, since there may be a more optimal and less cumbersome way to do it. Greetings.