CODEWITHSUNDEEP
sqlsql-serverdatabasemerge
talendguy
talendguy

Reputation: 81

SQL:Merge two different query

I am pretty new at SQL! I have two querys from different tables. This is my first query:

SELECT cli.CLIENTE as clientet,cli.RAZON as nom,
       SUM(distinct par.[RECAPTACIO]*0.9) AS recaudacionSINIVA,
       SUM(distinct par.canvi) as cambiocargado,
       SUM(distinct par.consum) as CC_recaptacio,count(distinct par.DATA) as visita,
       SUM((c.quantitat + c.caixes * c.uxcaja) * c.pvp) as CC_Caducados

FROM [V30].[dbo].[parmaq] as par,[V30].[dbo].[clientes] as cli , V30.dbo.parcons as c 
     where cli.CLIENTE=par.EMPRESA and par.enllas=c.enllas
     and par.data > '13-11-2015' and par.data < '20-11-2015'
     group by cli.CLIENTE,cli.RAZON

And this is the result: enter image description here

This is my second query:

SELECT cli.cliente as clientet, cli.RAZON, SUM( distinct m1.IMPORTE) as FACTURACION,
       SUM(distinct m2.coste * m2.canti) as CC_facturacio
FROM [V30].[dbo].[clientes] as cli, [V30].[dbo].[MESTA1] as m1,V30.dbo.MESTA2 as m2
       where m2.albaran=m1.albaran and m1.CLIENTE=cli.CLIENTE 
       and m1.FECHA > '13-11-2015' and m1.FECHA < '30-11-2015'  
       group by cli.CLIENTE,cli.razon order by cli.cliente

And the result of the second query: enter image description here

On the sql there is no way of linking the tables with primary key! and the results from the querys one and two are fine. What I want to do is combining the results using the column "clientet". I have to say that you can get different numbers of rows.

The goal is to have all this information on the same result. For example, if "clientet" have results on both querys, the final result should be all the columns from the first query + "FACTURACION" and "CC_facturacio" from the second query.

Hope you can help me, I have been trying Inner Joins and sub-querys but there is no way to get this.

Upvotes: 0

Views: 65

Answers (1)

nr99
nr99

Reputation: 46

Two options:

  1. As jarlh suggested, you could do a union as long as you put place holders in each query for the missing columns that don't exist and make sure they are in the same order.

  2. You could do a full outer join of your two queries. You may want to add coalesce to each other column, depending on whether you want nulls to appear for columns when a row doesn't exist in both tables.

The example below assumes clientet is the joining column:

 SELECT COALESCE(TBL_1.clientet, TBL_2.clientet) as clientet
    , TBL_1.nom
    , TBL_1.recaudacionSINIVA
    , TBL_1.cambiocargado
    , TBL_1.CC_recaptacio
    , TBL_1.visita
    , TBL_1.CC_Caducados
    , TBL_2.FACTURACION
    , TBL_2.CC_facturacio
 FROM

      (SELECT cli.CLIENTE as clientet
            , cli.RAZON as nom
           , SUM(distinct par.[RECAPTACIO]*0.9) AS recaudacionSINIVA
           , SUM(distinct par.canvi) as cambiocargado
           , SUM(distinct par.consum) as CC_recaptacio
           , count(distinct par.DATA) as visita
           , SUM((c.quantitat + c.caixes * c.uxcaja) * c.pvp) as CC_Caducados     
      FROM [V30].[dbo].[parmaq] as par,[V30].[dbo].[clientes] as cli , V30.dbo.parcons as c 
           where cli.CLIENTE=par.EMPRESA and par.enllas=c.enllas
           and par.data > '13-11-2015' and par.data < '20-11-2015'
           group by cli.CLIENTE,cli.RAZON) TBL_1

 FULL OUTER JOIN

      (SELECT cli.cliente as clientet
           , cli.RAZON
           , SUM( distinct m1.IMPORTE) as FACTURACION
           , SUM(distinct m2.coste * m2.canti) as CC_facturacio
      FROM [V30].[dbo].[clientes] as cli, [V30].[dbo].[MESTA1] as m1,V30.dbo.MESTA2 as m2
             where m2.albaran=m1.albaran and m1.CLIENTE=cli.CLIENTE 
             and m1.FECHA > '13-11-2015' and m1.FECHA < '30-11-2015'  
             group by cli.CLIENTE,cli.razon) TBL_2

   ON TBL_1.clientet = TBL_2.clientet
 ORDER BY COALESCE(TBL_1.clientet, TBL_2.clientet)

Upvotes: 1

Related Questions