compare sql tables displaying different result

Asked

Viewed 48 times

0

I have an sql query that lists two tables:

register and release.

I have an application that adds the "id" of "registration" in the "release" table, i intend to display only the results that DO NOT exist in the table "release".

follow the consultation:

$verifica = $db->prepare("SELECT liberar.aluno AS alunoID, liberar.title, cadastro.nome AS name, cadastro.id AS idAluno FROM cadastro INNER JOIN liberar ON (cadastro.id = liberar.aluno) WHERE liberar.title = ? GROUP BY cadastro.nome ORDER BY cadastro.nome");
    $verifica->execute(array($identificador));

This $identifier variable is the title I captured by GET. this query returns the ids that were added in release, but I want to return the ones that are not added only.

I already tried to change (registration.id != release.student) but so it returns me all id, I need to return only the ids that were sent to release..

php sql

3 answers

1


The query below returns all entries that do not exist record in the release table:

SELECT
    cadastro.id
 FROM
     cadastro
 WHERE
       NOT EXISTS ( SELECT * FROM liberar where liberar.aluno =  cadastro.id )
 GROUP BY
     cadastro.nome
 ORDER BY
     cadastro.nome

However there is no way to filter by releasing.title because the title exists only in the release table, and in the records you want there are no items in the release tables.

  • that solved, thank you! I posted the adaptation of my code.

0

Solved with Marciano Machado’s tip, thank you!

NOT EXISTS (SELECT * FROM release WHERE student = registration.id )

"SELECT cadastro.*, liberar.* FROM cadastro INNER JOIN liberar WHERE liberar.title = ? AND NOT EXISTS (SELECT * FROM liberar WHERE aluno =  cadastro.id ) GROUP BY cadastro.nome ORDER BY cadastro.nome"
$verifica->execute(array($identificador));

I adapted it this way: includes the joining of the tables to retrieve the "title" as it is the title that I recover by the variable $identifier in GET, this variable filters the query by title, since the application will have titles based on school subjects.

0

I’ll put two examples where I want customers who have no sales

1 - 

SELECT DISTINCT.* FROM CLIENTES C
LEFT JOIN VENDAS V ON(V.CODID = C.CODID)
WHERE (V.CODID IS NULL)

2 - 

SELECT DISTINCT C.* FROM CLIENTES
WHERE (CODID NOT IN(SELECT CODID FROM VENDAS))

The two bring the same result, what changes is the performance depending on the amount of records in each table

Assuming the CUSTOMERS table is huge, the first example will be slower, if it is SALES the second example will be slower

Account must include the INDEXES created in each table and the additional filters in the WHERE clause

Browser other questions tagged php sql

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