CODEWITHSUNDEEP
mysql
Gabriel Santos
Gabriel Santos

Reputation: 4974

Mysql Input/Output query

I have two querys:

  SELECT LancamentoEntrada.*,
         TipoEntrada.descricao AS nome,
         Usuario.nome AS obreiro
    FROM lancamento_entradas LancamentoEntrada,
         tipo_entradas TipoEntrada,
         obreiros Obreiro,
         usuarios Usuario
   WHERE LancamentoEntrada.tipo_entrada_id = TipoEntrada.id
     AND TipoEntrada.somar_caixa = 1
     AND LancamentoEntrada.obreiro_id = Obreiro.id
     AND Usuario.id = Obreiro.usuario_id
     AND LancamentoEntrada.data_entrada >= '{$begin}'
     AND LancamentoEntrada.data_entrada <= '{$end}'
ORDER BY LancamentoEntrada.data_entrada

And

  SELECT LancamentoSaida.*,
         TipoSaida.descricao AS nome
    FROM lancamento_saidas LancamentoSaida,
         tipo_saidas TipoSaida
   WHERE LancamentoSaida.tipo_saida_id = TipoSaida.id
     AND TipoSaida.somar_caixa = 1
     AND LancamentoSaida.data_saida >= '{$begin}'
     AND LancamentoSaida.data_saida <= '{$end}'
ORDER BY LancamentoSaida.data_saida

Which generate the follow arrays:

// Query 1
Array(
    [0] => Array (
        [id] => 3
        [tipo_entrada_id] => 1
        [data_entrada] => 2012-05-08
        [data_vencimento] => 2012-05-08
        [obreiro_id] => 2
        [valor_pago] => 20.00
        [valor_pagar] => 0.01
        [observacoes] => TESTE
    )

    [1] => Array (
        [...]
    )
)

// Query 2
Array (
    [0] => Array (
        [id] => 1
        [tipo_saida_id] => 1
        [data_saida] => 2012-05-08
        [data_vencimento] => 2012-05-08
        [valor_pago] => 200.00
        [observacoes] => tESTE
    )
    [1] => Array (
        [...]
    )
)

But, I want to do one query, listing inputs and outputs, how I can acomplish this? If need more explanation, please, ask-me.

EDIT 1

inputs are generated from first query, output from second.

EDIT 2

The querys need to generate report of financial input/output, so, the first query get all input stored and the second get all output generated, both betwenn from one period. I need to generate a list with all, input and output, ordered by date.

Edit 3

I have done this query, the problem is, how I know when is input and when is output?

Tried ISNULL and CASEs, but not work.

(SELECT LancamentoEntrada.data_entrada AS data,
    LancamentoEntrada.data_vencimento AS vencimento,
    LancamentoEntrada.valor_pago AS valor,
    LancamentoEntrada.observacoes AS observacoes,
    TipoEntrada.descricao AS nome
   FROM lancamento_entradas LancamentoEntrada,
    tipo_entradas TipoEntrada
  WHERE LancamentoEntrada.tipo_entrada_id = TipoEntrada.id
    AND TipoEntrada.somar_caixa = 1
)

UNION

(SELECT LancamentoSaida.data_saida AS data,
    LancamentoSaida.data_vencimento AS vencimento,
    LancamentoSaida.valor_pago AS valor,
    LancamentoSaida.observacoes AS observacoes,
    TipoSaida.descricao AS nome
   FROM lancamento_saidas LancamentoSaida,
    tipo_saidas TipoSaida
  WHERE LancamentoSaida.tipo_saida_id = TipoSaida.id
    AND TipoSaida.somar_caixa = 1
)

Upvotes: 0

Views: 787

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52675

If the only thing you still need is to identify which records came from which query you just need to add a literal to each query.

( SELECT 
      'Input' as rec_type,

     LancamentoEntrada.data_entrada AS data,
    LancamentoEntrada.data_vencimento AS vencimento,
    LancamentoEntrada.valor_pago AS valor,
    LancamentoEntrada.observacoes AS observacoes,
    TipoEntrada.descricao AS nome
   FROM lancamento_entradas LancamentoEntrada,
    tipo_entradas TipoEntrada
  WHERE LancamentoEntrada.tipo_entrada_id = TipoEntrada.id
    AND TipoEntrada.somar_caixa = 1
)

UNION ALL

(SELECT 
      'Output' as rec_type,

      LancamentoSaida.data_saida AS data,
    LancamentoSaida.data_vencimento AS vencimento,
    LancamentoSaida.valor_pago AS valor,
    LancamentoSaida.observacoes AS observacoes,
    TipoSaida.descricao AS nome
   FROM lancamento_saidas LancamentoSaida,
    tipo_saidas TipoSaida
  WHERE LancamentoSaida.tipo_saida_id = TipoSaida.id
    AND TipoSaida.somar_caixa = 1
)

As an aside you'll get better performance if you UNION ALL Since UNION would remove duplicates from the two sets which you won't have in this case.

Upvotes: 1

Related Questions