Error giving a select in two tables

Asked

Viewed 62 times

0

I’m trying to make a select on these two tables, but this bringing wrong data, I want to do the following, list the loan requests that have not yet been rented, ie if the codigo_socio within the loaned table it is not shown to the user and lists the remainder of requests that are not in the loaned table but in the solicitaçãoemprestimo.

EMPRESTADOS
codemprestados
dataemprestimo
horaemprestimo
solicitacaoemprestimo_socio_codigo



SOLICITACAOEMPRESTIMO
codemprestimo
dataemprestimo
horaemprestimo
socio_codigo

I’m trying to do it like 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 bringing field that is inside the borrowed, I wanted you not to bring the item but to filter what is already on the borrowed table. Already I thank you.

mysql

3 answers

0

If I understand what you need, try adding NOT IN to 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'
);

0

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 */

  • So let’s say that in the loan application I have three requests, then I sent one to the borrowed table, when I list I want to appear only the items that are not within the borrowed table, as I lent would have to appear only two items.

  • In this example I tried and it brings nothing.

  • @Carlosleandroferreiradealm I changed here again. The column codemprestimo is the same as codemprestados of Lent?

  • I updated SELECT with this column.

  • No, it’s not, but I need to bring by the partner’s code, by the codemprestimo I got too.

  • @Carlosleandroferreiradealm That’s not just for doing where e.codemprestados is null AND se.socio_codigo = '41' ?

  • then, like: select if. * from solicitacaoemprestimo se left Join lent e on.codemprestados = se.codemprestimo Where e.codemprestados is null AND se.socio_codigo = '41' ; it brings what is inside the borrowed one I need it to bring in the ones that are not borrowed.

  • From the moment you do e.codemprestados is null It will only bring what it does not have on the table borrowed, if it is bringing wrong may be incorrect data in the table. Put an example on Sqlfiddle for us to check

  • what is a sqlFiddle?

  • @CarlosLeandroFerreiradeAlm http://sqlfiddle.com/

  • On the left side you place the create table and Inserts (do some Inserts that exist in your bank) and on the right side will select

  • I’ve already done it and it only brings what’s inside the borrowed table.

  • then I changed a parameter and it worked. It looked like this: select if. * from solicitacaoemprestimo se left Join lent e on.solicitacaoemprestimo_codemprestimo = se.codemprestimo Where e.codemprestados is null AND se.socio_codigo = '41';

  • changed this part of the code: on e.requecamprestimo_codemprestimo = se.codemprestimo

  • Thanks Maicon, thank you!

  • True, I hadn’t noticed that, I’m glad it worked out :)

  • @Carlosleandroferreiradealm I updated my answer with the error you found.

Show 12 more comments

0

Look, you’re a little confused, you don’t want the bank to list when the membership code is filled in as I understand it, so that’s how it would be:

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

But thinking about it doesn’t work, I think the right thing would be for you to create the key codemprestimo inside the table Emprestados and from there list all SOLICITACAOEMPRESTIMO which do not contain the codigo_socio

Example:

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

Browser other questions tagged mysql

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