find reference in another table

Asked

Viewed 86 times

0

I have the following tables:

  1. register
  2. batches

Where the cadastro contains the primary key id_cadastro that us lotes refers to it.

However, the bank was not standardised, so the foreign key that refers to id_cadastro there is no.

I am manually registering, but it is almost impossible to locate all references that do not exist.

Is there any way I can search for missing references automatically with SQL commands?

mysql

  • Put the complete structure of the tables

3 answers

1

I guess that should do it

select * from lotes where lotes.id_cadastro not in (select id_cadastro from cadastro)

0

There are two ways to do this, the first is this one:

SELECT * FROM lotes a
WHERE a.id_cadastro NOT IN (
    SELECT id_cadastro FROM cadastro
);

and the second is this:

SELECT * FROM lotes a
LEFT JOIN cadastro b ON a.id_cadastro = b.id_cadastro
WHERE b.id_cadastro IS NULL;

To segunda opção has a huge performance gain on the primeira opção.

0

I managed to solve with the following code

    SELECT  *
FROM    lotes l
WHERE   NOT EXISTS
        (
        SELECT  null 
        FROM    cadastro c
        WHERE    c.id_cadastro = l.id_cadastro
        )

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.