Select Inner Join from what is not on the other table

Question

Select Inner Join from what is not on the other table

Navigation

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

2

I have two tables, Anuidades and Pagamentos .
From these annuities it is necessary to select data from Anuidades that have not yet been paid.

Table Anuidades

TablePagamentos

ToreturntheannuitydatathathadbeenpaidIusedthefollowingquery:

SELECT*FROManuidadesINNERJOINpagamentosONanuidades.Id=pagamentos.idAnuidadeWHEREpagamentos.idMinistro=1

anditreturnedcorrectly:

But when I try to select the annuities that are not registered in the payment table I can not.

php mysql

asked by anonymous 19.11.2018 / 05:18

2 answers

4

Try this:

SELECT anuidades.* 
FROM anuidades
LEFT JOIN pagamentos ON anuidades.Id = pagamentos.idAnuidade
WHERE pagamentos.idMinistro = 1
AND pagamentos.idAnuidade Is null

19.11.2018 / 06:17

Git and GitHub: Need to give remote more than once? how to position a triangle created with css?

score 0 · Accepted Answer

There are more than one way to do this select, here are some ways:

LEFT OUTER JOIN

SELECT * FROM ANUIDADES
LEFT JOIN PAGAMENTOS 
    ON ANUIDADES.Id = PAGAMENTOS.idAnuidade
WHERE PAGAMENTOS.idAnuidade IS NULL

NOT EXISTS

SELECT * FROM ANUIDADES
WHERE NOT EXISTS (
            SELECT NULL FROM PAGAMENTOS
                WHERE   PAGAMENTOS.IDMINISTRO = 1
                AND     PAGAMENTOS.IDANUIDADE = ANUIDADES.ID)

NOT IN

SELECT * FROM ANUIDADES
WHERE ANUIDADES.ID NOT IN (
                        SELECT IDANUIDADE FROM PAGAMENTOS
                        WHERE IDMINISTRO = 1)

Note: LEFT JOIN / IS NULL and NOT EXISTS are semantically equivalent, whereas NOT IN is not.