Using data from one select within another?

Question

Using data from one select within another?

Navigation

#1 by (1 votes)

2

Two tables tbl_orcamento and tbl_itens the table budgets have a column with the following statuses.

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

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

id_tbl_orcamento
data_received
n_nf_received

and the query I am trying to do is to know if all of the order items have arrived, the way I am using it 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 think that in the second SELECT the statement is not understanding id id_tbl_orcamento = tbl_orcamento_id , there is a way to pass the variable from the first statement to the second.

This is the% internal% of the corresponding SELECT main

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

You should check if the items referring to the request have nf number, and if it is equal to zero is still pending then the request is incomplete, if all are nf the value will be 100 and thus satisfy the query

My question is in this line of WHERE

id_tbl_origin = tbl_orcamento.id

Because I'm not passing a value, I'm trying to catch it dynamically. That way QUERY works.

id_tbl_orcamento = '123'

mysql query select inner

asked by anonymous 24.04.2018 / 20:35

1 answer

With loop use columns in objects in R Scroll-based loading bar

score 1 · Accepted Answer

Fixed using HAVING to query comparing 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 you only wanted to display 100% received requests, but if you only want to show data that has pending pick items you can use HAVING as follows.

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

Remember the HAVING needs to stay after GROUP BY , in this form as demonstrated.

You have a very good answer here in the OS that explains the operation of having See Here .