How to browse tuples for an ID and check their values?

Navigation
1

In a table where I record the number of parcels and the situation of each parcel, I need to go through it and find the sales orders where all parcels are paid.

Ex: 

SELECT * FROM tabela_parcelas WHERE ID = X

Return: 

ID       Parcela | Situacao  
123456 |  1      | Pago  
123456 |  2      | Pendente 
123456 |  3      | Pendente
000002 |  1      | Pago
000002 |  2      | Pago

@edit I need to check in the "table_parcels" if all portions of the XXX order have been paid, so in the "order_table" I can change the purchase status to "complete" or keep "pending."

The possible statuses of each installment are: 

// 00 - pagamento efetuado 
// 01 - pagamento não autorizado/transação recusada 
// 02 - erro no processamento da consulta 
// 03 - pagamento não localizado 
// 10 - campo “idConv” inválido ou nulo 
// 11 - valor informado é inválido, nulo ou não confere com o valor registrado 
// 21 - Pagamento Web não autorizado 
// 22 - erro no processamento da consulta 
// 23 - erro no processamento da consulta
// 24 - Convênio não cadastrado 
// 25 - Convênio não ativo 
// 26 - Convênio não permite debito em conta 
// 27 - Serviço inválido 
// 28 - Boleto emitido 
// 29 - pagamento não efetuado
// 30 - erro no processamento da consulta 
// 99 - Operação cancelada pelo cliente
    
asked by anonymous 20.10.2014 / 22:45

3 answers

0

Resolved as follows: 

SELECT * FROM 'tabela_parcelas ' A1 WHERE NOT EXISTS ( SELECT * from 'tabela_parcelas ' A2 where A1.id = A2.id and A2.situacao <> 0 ) ORDER BY id desc
    
21.10.2014 / 21:47
3

If ID is numeric 

SELECT * FROM tabela_parcelas WHERE ID = 2 AND Situacao="Pago"

if string 

SELECT * FROM tabela_parcelas WHERE ID = "000002" AND Situacao="Pago"


Edit: After your comment and trying to guess what you need, maybe it's something in that line ( just example, of course . 

SELECT * FROM tabela_ordens WHERE (SELECT tabela_parcelas (NAO-RETORNA-NADA-PENDENTE") )

I await your edit in the question to improve the answer (or for other participants to answer, of course).

    
20.10.2014 / 22:48
0

Instead of looking for everything that has already been paid, why not look for everything that has nothing outstanding? 

SELECT * FROM tabela_parcelas WHERE id NOT IN 
(SELECT id FROM tabela_parcelas WHERE situacao = 'Pendente');

Example in sqlfiddle

I'm assuming there are only two situations: Pago e Pendende. If there are others, in% with_internal% you have to put the other situations using a select

According to the status codes posted, as they are numeric according to your comment, simply change the query situation field to greater than 0. 

SELECT * FROM tabela_parcelas WHERE id NOT IN 
(SELECT id FROM tabela_parcelas WHERE situacao > 0);
    
21.10.2014 / 00:06
How to use foreign key in SQL statements? Translation Laravel 4