Combine SQL queries with different columns

Asked

Viewed 137 times

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

and, 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: to each cod_reduzido of Q2 will have n tuples of Q1 (who possess the same cod_reduzido), how could I solve this problem of mine?

sql-server
  • 1

    You need the table of Q1 (finempe) relates to any of the tables of Q2 (orcdotac or orcplade). Is there a field that does that? Like finempe.id_orcdotac

  • @rLinhares dude, there is no field that does this, at most cod_reduced is equal for both, but these two tables belong to the same BD

  • That’s why cod_reduzido that you know that an occurrence of Q1 relates to x occurrences of Q2? If so, this guy will be used. (if yes, in which of the two tables of Q2 it is?)

  • As for what you said about "these two tables belong to the same BD", it doesn’t mean they have relation; for example I can have the table usuarios and the cores_produto that do not relate to anything. Understand?!

  • it is in both tables, both in Q1 and Q2, I will display in an html table, where Q2 would be like a title and Q1 the tuples with the information, but it has N titles and N information.

  • @rLinhares , I understand... so basically they have no relationship, but at the same time it has, like a paradox same

Show 1 more comment

1 answer

0

updated response

I believe the idea is to turn the two into one, since you need data of the two. 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 am admitting that the cod_reduced field exists both in the table finempe how much in the orcdotac. If it does not exist in the second, we can reformulate the joins.

  • 1

    is the following, I would like the query to return tbm the "titles" of Q2, this query returned me the same result of Q1 only that with the extra columns, I would like something like: Q2 (cod_reduced = 1) - (all tuples of Q1 that cod_reduced = 1) Q1, Q1, Q1- Q2 (cod_reduced = 2) -here has no tuples of Q1 that cod_reduced = 2- Q2 (cod_reduced = 3)- Q1... gave to understand?

  • 1

    guess not :D if in Q2 you know who should be shown in Q1 through the field cod_reduzido, then it must be the union field between the tables. So, if the cod_reduzido is unique in the table orcdotac, it’s always gonna be one-on-one.. Check out?!

  • in Q2 will have a (1) tuple with each cod_reduced, but in Q1 will have n tuples with the same cod_reduced, where: n >= 0

  • Got it, but then what’s the mistake? From what I understand is returning the same data of Q1 with the fields of Q2, which is expected

  • when Q1 has no tuple, nothing is displayed, which, was to display the data of Q2

  • in case you need the data of Q2 independently? may happen to exist a guy in Q1 and not have the corresponding in Q2?

  • this, always appears Q2 and if there are in Q1 tuples that have the same cod_reduced, they should appear just below Q2

  • I’ll edit the answer and you test

Show 3 more comments

Browser other questions tagged sql-server

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