Merge SQL queries with different columns

Question

Merge SQL queries with different columns

Navigation

#1 by (0 votes)

1

I have the following queries, q1:

SELECT finempe.codigo_orgao
    ,finempe.cod_reduzido
    ,finempe.num_empenho
    ,finempe.data_empenho
    ,finempe.nome_fornecedor
    ,finempe.valor_empenhado 
    ,finempe.valor_anulado
    ,finempe.valor_pago
    ,finempe.valor_liquidado
    ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_liquidado as decimal(18,2)) as sld_a_liquidar
    ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_pago as decimal(18,2)) as sld_a_pagar
  FROM [Cont98].[dbo].[finempe]
  where finempe.codigo_orgao = 01   and finempe.data_empenho between '01-01-1998' and  '31-12-1998'  
  order by finempe.cod_reduzido, finempe.num_empenho

e, q2:

SELECT cod_reduzido
      ,sld_orc_ano
      ,sld_supl_ano
      ,sld_esp_ano
      ,sld_ext_ano
      ,sld_re_ano
      ,orcplade.descricao
  FROM Cont98.dbo.orcdotac
  inner join orcplade on
  (orcdotac.conta_desp = orcplade.conta_desp)
  WHERE codigo_orgao = 01  and cod_reduzido != 0
  ORDER BY cod_reduzido

I would like to combine them as follows: every% of q2 will have n tuples of q1 (which have the same cod_reduzido ), how could you solve this problem for me?

sql-server

asked by anonymous 09.01.2018 / 17:22

1 answer

How to change the conjunction between the authors of the hepthesis model in Overleaf? Update DIV with Ajax Asp.Net C #

score 0 · Answer 1

updated response

I think the idea is to turn the two into one, since you need data from both. Try this query below:

SELECT finempe.codigo_orgao
    ,finempe.cod_reduzido
    ,finempe.num_empenho
    ,finempe.data_empenho
    ,finempe.nome_fornecedor
    ,finempe.valor_empenhado 
    ,finempe.valor_anulado
    ,finempe.valor_pago
    ,finempe.valor_liquidado
    ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_liquidado as decimal(18,2)) as sld_a_liquidar
    ,cast(finempe.valor_empenhado as decimal(18,2)) - cast(finempe.valor_anulado as decimal(18,2)) - cast(finempe.valor_pago as decimal(18,2)) as sld_a_pagar
    /*,cod_reduzido (não precisa pois será igual ao primeiro campo)*/
    ,sld_orc_ano
    ,sld_supl_ano
    ,sld_esp_ano
    ,sld_ext_ano
    ,sld_re_ano
    ,orcplade.descricao
FROM Cont98.dbo.orcdotac
INNER JOIN orcplade on (orcdotac.conta_desp = orcplade.conta_desp)
LEFT JOIN Cont98.dbo.finempe on (finempe.cod_reduzido = orcdotac.cod_reduzido)
WHERE finempe.codigo_orgao = 01
  AND finempe.data_empenho BETWEEN '01-01-1998' AND '31-12-1998'
  AND orcdotac.cod_reduzido != 0
ORDER BY cod_reduzido, finempe.num_empenho

Detail: I'm assuming the cod_reduced field exists in both the finempe and the orcdotac tables. If it does not exist in the second, we can reshape joins .