How can you get data from a table 1 that is not in table2?

Question

How can you get data from a table 1 that is not in table2?

Navigation

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

-2

As I can do to obtain the data from a table1 that is not in another table2 and that is different from an id (idmodselection) that is in table2, I am working on 2 tables that are related, which are:

Tabla 1: requisitos
idrequisito int (Clave primaria),
nombrerequisito varchar(25)

Tabla 2: modalidad_requisito
idmodrequisito int (Clave primaria),
idmodseleccion int,
idrequisito int

I was testing with the sql statement that is not found (not exists)

SELECT * FROM requisitos r 
WHERE NOT EXISTS 
  (SELECT * FROM modalidad_requisitos mr 
   WHERE mr.idrequisito=r.idrequisito AND mr.idmodseleccion!=10)

but the detail is when the condition that is different to the idmodseleccion, if you could give me an idea I would appreciate it.

mysql sql

asked by Teresa Agui 18.09.2018 в 18:12

source

2 answers

Execution of a client program in the background every certain period of time Problem with routes in Laravel when going to server

score 0 · Answer 1

Try replacing this:

SELECT * FROM requisitos r 
WHERE NOT EXISTS 
(SELECT * FROM modalidad_requisitos mr 
WHERE mr.idrequisito=r.idrequisito AND mr.idmodseleccion!=10)

Because of this:

SELECT * FROM requisitos r 
WHERE NOT EXISTS 
(SELECT * FROM modalidad_requisito mr 
WHERE mr.idrequisito=r.idrequisito AND mr.idmodseleccion<>10)

Eye! It is "reimbursement modality" and NOT "reimbursement modality"

score 0 · Answer 2

If what you are looking for are the elements of a table that are NOT in another table, I think you should use the NOT IN statement:

SELECT * FROM Requisitos r
WHERE r.idrequisito NOT IN (
    SELECT idrequisito FROM modalidad_requisito mr
    WHERE mr.idmodseleccion <> 10
)

This takes all the request mode ids that meet a certain condition (in the example, idmodselection is different from 10). Then, the main query looks for all the REQUIREMENTS that DO NOT have any of the indexes that you have obtained in the subquery.