Using data from one select within another?

Asked

Viewed 864 times

2

Two tables tbl_orcamento and tbl_itens the budget table has a column with the following status.

1 = orcamento aprovado
2 = ordem de compra emitida
3 = ordem de compra aprovada

To tbl_itens has some columns I will list the ones that will be used in the query.

  • id_tbl_budget
  • date of receipt
  • n_nf_received

and query I am trying to make is to know if all the items of the request have arrived, the way I am using is below.

SELECT * FROM tbl_orcamento
INNER JOIN tbl_itens ON tbl_orcamento.id = tbl_itens.id_tbl_orcamento
WHERE (SELECT COUNT(q.id) / t.total * 100 FROM tbl_itens q, 
(SELECT COUNT(*) as total FROM tbl_itens 
WHERE id_tbl_orcamento = tbl_orcamento.id ) t 
WHERE id_tbl_orcamento = tbl_orcamento.id AND n_nf_recebida != 0) = 100.0000

I believe that in the second SELECT the instruction is not understanding the id id_tbl_budget = tbl_budget, there is a way to pass the variable from the first instruction to the second.

This is the SELECT internal corresponding to the WHERE leading

SELECT COUNT(q.id) / t.total * 100 FROM tbl_itens q, 
(SELECT COUNT(*) as total FROM tbl_itens 
WHERE id_tbl_orcamento = tbl_orcamento.id ) t 
WHERE id_tbl_orcamento = tbl_orcamento.id AND n_nf_recebida != 0

Which must check if the items referring to the request have number of nf, and if it is equal to zero still this pending then the request is incomplete, if all are with number of nf the value will be 100 and so satisfy the query.

My doubt lies in this line of QUERY

id_tbl_budget = tbl_budget.id

'Cause I’m not passing a figure, I’m trying to take it dynamically. Thus the QUERY works.

id_tbl_budget = '123'

mysql query select inner

  • Do you get any errors? I don’t understand the problem

  • I receive NULL, but if I do the internal query stating a valid id I receive the percentage value of items received and if everything has been received I receive 100.0000

  • But you get NULL in which query? can further specify your problem in the question?

  • I receive null in the question statement. I am suspicious that the second select is not receiving the id number of the table tbl_orcing.

  • I edited the question I hope to understand

  • Try to do it this way: SELECT * FROM tbl_orcamento a
INNER JOIN tbl_itens b ON a.id = b.id_tbl_orcamento
WHERE (
 SELECT SUM(IF(a1.n_nf_recebida != 0, 1, 0)) / COUNT(a1.id) * 100
 FROM tbl_itens a1
 WHERE a1.id_tbl_orcamento = a.id
) = 100;

Show 1 more comment

1 answer

1


Solved using HAVING the query comparing the variables noc and nocs

The query was as follows

SELECT *, COUNT(tbl_itens.data_recebido) as noc, COUNT(tbl_itens.n_nf_recebida) as nocs 
FROM tbl_orcamento
INNER JOIN tbl_itens ON tbl_orcamento.id = tbl_itens.id_tbl_orcamento
WHERE tbl_orcamento.status = 1
GROUP BY tbl_itens.id_tbl_orcamento
HAVING noc = nocs // compara os valores se forem iguais os itens foram recebidos
ORDER BY tbl_orcamento.id ASC

As I just wanted to display the orders that form 100% received, but if you want to show only the data that still have pending receiving items you can use the HAVING as follows.

HAVING noc != nocs //dessa forma ele carrega apenas os dados que ainda possuem ítens pendente de recebimento.

Remember the HAVING need to stay after GROUP BY, in this way as demonstrated.

It has a very good answer here in the OS that explains the functioning of the having See Here.

Browser other questions tagged mysql query select inner

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