Right Outer Join Problem

• rootfixxxer

  SSCrazy

  Points: 2918

  More actions

  March 6, 2015 at 5:41 am

  #295025

  Hello

  Need some help, i have a long query that in the end it will be joined with another one with a right outer join, but i don't know why, it doesn't work.

  I wrote some sample data, and it works fine, in the real data nothing...

  So first the real thing with the outputs, cannot put sample values here, it's too much info:

  SELECT MET.obra,MET.pa, TOTAL = SUM(MET.metros)

  FROM(

  SELECT obra,pa,metros FROM @TabelaConsumidos TC

  UNION ALL

  SELECT obra,pa,metros FROM @TabelaEmFabrico TF

  ) MET

  GROUP BY MET.obra,MET.pa

  -- OUTPUT

  SELECT 245,4,824.97

  UNION ALL

  SELECT 245,5,2537.69

  UNION ALL

  SELECT 245,6,757.39

  UNION ALL

  SELECT 245,7,1.06

  UNION ALL

  SELECT 245,8,6.54

  UNION ALL

  SELECT 245,9,1.14

  UNION ALL

  SELECT 245,15,131.00

  UNION ALL

  SELECT 245,17,726.80

  UNION ALL

  SELECT 245,23,3654.18

  UNION ALL

  SELECT 245,24,10959.07

  --- SECOND QUERY

  SELECT

  FRD.Obra

  ,OB.NomeCompleto

  ,OB.idObra

  ,PF.produtoAcabado

  ,[Hours] = SUM(FRD.Horas)

  FROM dbo.FabricoRegistoDiario FRD

  INNER JOIN dbo.Obra OB

  ON CAST(OB.NumeroObra AS INTEGER) = FRD.Obra

  INNER JOIN dbo.PF

  ON PF.obra = OB.idObra AND PF.NumeroPF = FRD.PF

  WHERE OB.idObra = 245

  GROUP BY

  FRD.Obra

  ,OB.NomeCompleto

  ,OB.idObra

  ,PF.produtoAcabado

  ORDER BY PF.produtoAcabado

  --- OUTPUT SECOND

  SELECT 2139,'2139 - Project 2139',245,4,367.75

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,5,785.00

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,8,29.25

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,15,1.25

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,17,511.25

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,23,5640.00

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,24,2092.00

  --- JOIN

  SELECT

  FRD.Obra

  ,OB.NomeCompleto

  ,OB.idObra

  ,PF.produtoAcabado

  ,TOT.METERS

  ,[Hours] = SUM(FRD.Horas)

  FROM dbo.FabricoRegistoDiario FRD

  INNER JOIN dbo.Obra OB

  ON CAST(OB.NumeroObra AS INTEGER) = FRD.Obra

  INNER JOIN dbo.PF

  ON PF.obra = OB.idObra AND PF.NumeroPF = FRD.PF

  RIGHT OUTER JOIN (SELECT [OB] = MET.obra, [PA] = MET.pa, [METERS] = SUM(MET.metros)

  FROM(

  SELECT obra,pa,metros FROM @TabelaConsumidos TC

  UNION ALL

  SELECT obra,pa,metros FROM @TabelaEmFabrico TF

  ) MET

  GROUP BY MET.obra,MET.pa) TOT

  ON TOT.pa = PF.produtoAcabado AND OB.idObra = TOT.OB

  WHERE OB.idObra = 245

  GROUP BY

  FRD.Obra

  ,OB.NomeCompleto

  ,OB.idObra

  ,PF.produtoAcabado

  ,TOT.METERS

  ORDER BY PF.produtoAcabado

  --- OUTPUT THIRD

  SELECT 2139,'2139 - Project 2139',245,4,824.97,367.75

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,5,2537.69,785.00

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,8,6.54,29.25

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,15,131.00,1.25

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,17,726.80,511.25

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,23,3654.18,5640.00

  UNION ALL

  SELECT 2139,'2139 - Project 2139',245,24,10959.07,2092.00

  Some PA codes, are missing, the 6, 7 and 9

  SAmple code:

  DROP TABLE #Teste1

  CREATE TABLE #Teste1 (

  id1 INTEGER,

  id2 INTEGER,

  valor NUMERIC(18,2)

  )

  -- 10 rows

  INSERT INTO #Teste1

  SELECT 245,4,824.96

  UNION ALL

  SELECT 245,5,2537.32

  UNION ALL

  SELECT 245,6,757.39

  UNION ALL

  SELECT 245,7,1.06

  UNION ALL

  SELECT 245,8,6.54

  UNION ALL

  SELECT 245,9,1.14

  UNION ALL

  SELECT 245,15,131.00

  UNION ALL

  SELECT 245,17,726.80

  UNION ALL

  SELECT 245,23,3654.18

  UNION ALL

  SELECT 245,24,10958.07

  DROP TABLE #Teste2

  CREATE TABLE #Teste2(

  Nam NVARCHAR(50),

  id11 INTEGER,

  id21 INTEGER,

  total NUMERIC(18,2)

  )

  -- 7 rows

  INSERT INTO #Teste2

  SELECT 'Name 1',245,4,366.75

  UNION ALL

  SELECT 'Name 1',245,5,772.75

  UNION ALL

  SELECT 'Name 1',245,8,14.25

  UNION ALL

  SELECT 'Name 1',245,15,1.25

  UNION ALL

  SELECT 'Name 1',245,17,511.25

  UNION ALL

  SELECT 'Name 1',245,23,5640.00

  UNION ALL

  SELECT 'Name 1',245,24,2051.25

  SELECT id1 ,id2 ,valor,T2.total FROM #Teste1 T1 FULL OUTER JOIN #Teste2 T2 ON T1.id1 = T2.id11 AND T1.id2 = T2.id21

  THanks for the help