Error giving a select in two tables

Question

Error giving a select in two tables

Navigation

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

0

I'm trying to do a select in these two tables, but this one bringing up wrong data, I want to do the following, list the loan requests that have not yet been rented, ie if the codigo_socio is in the table borrowed it stays without showing to the user and list the rest of requests that are not in the table loaned but in solicitaçãoemprestimo .

EMPRESTADOS
codemprestados
dataemprestimo
horaemprestimo
solicitacaoemprestimo_socio_codigo



SOLICITACAOEMPRESTIMO
codemprestimo
dataemprestimo
horaemprestimo
socio_codigo

I'm trying to do this:

select se.* from solicitacaoemprestimo se
left join emprestados e on e.solicitacaoemprestimo_socio_codigo = se.socio_codigo
where e.solicitacaoemprestimo_socio_codigo = '41';

But this field that is inside the loan, I wanted it not to bring the item but rather to filter what is already in the table borrowed. Thank you in advance.

mysql

asked by anonymous 08.06.2015 / 15:49

3 answers

Generate sequential number in java How do I get into several different files

score 0 · Answer 1

Looks a bit confusing, you do not want the bank to list when the social_code is populated by what I understand, so it would look like this:

select se.* from solicitacaoemprestimo se LEFT JOIN emprestados e on
e.solicitacaoemprestimo_socio_codigo = se.socio_codigo where
e.solicitacaoemprestimo_socio_codigo = '';

But thinking this does not work, I think the correct thing would be for you to create the codemprestimo key inside the Emprestados table and from there list all SOLICITACAOEMPRESTIMO that do not contain codigo_socio

Example:

select se.* from solicitacaoemprestimo se LEFT JOIN emprestados e on
e.codTabelaEmprestimo = se.codemprestimo where
e.solicitacaoemprestimo_socio_codigo = '';

score 0 · Answer 2

Would that be?

select se.* from solicitacaoemprestimo se
left join emprestados e on e.solicitacaoemprestimo_socio_codigo = se.codemprestimo
where e.codemprestados is null AND se.socio_codigo = '41'
/* retorna apenas o que existe na tabela solicitacaoemprestimo e não na emprestados e que o socio_codigo seja igual a 41 */

score 0 · Answer 3

If I understood correctly what you need, try adding NOT IN in the query.

SELECT * FROM solicitacaoemprestimo
WHERE socio_codigo = '41'
AND socio_codigo NOT IN (
SELECT solicitacaoemprestimo_socio_codigo FROM emprestador WHERE solicitacaoemprestimo_socio_codigo = '41'
);