Início / dba / Perguntas / 177022
Accepted
user2676140
Asked: 2017-06-23 07:47:37 +0800 CST

Cláusula Where com Full Outer Join

  • 772

Posso estar configurando minha consulta completamente incorreta, mas meu conjunto de resultados esperado que preciso retornar é Nuestra B & Nosotros Bporque eles se enquadram no intervalo de datas, respectivamente. IE Nuestra B feeduedate >= '20160301' AND fj.feeduedate <='20160330'e para Nosotros B fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'eles se encaixam.

No entanto, quando executo esta consulta tentando retornar o resultado, recebo 0 retornado no meu conjunto de resultados.

O que está incorreto na minha consulta? Abaixo estão dados de amostra e DDL

    CREATE TABLE [dbo].[fei](
    [violatorsName] [varchar](500) NOT NULL,
    [violationNumber] [varchar](100) NOT NULL,
    [violationDate] [date] NOT NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[FJI](
    [violatorsName] [varchar](500) NOT NULL,
    [VIN] [varchar](100) NOT NULL,
    [vfees1] [float] NOT NULL,
    [vfees2] [float] NOT NULL,
    [vfees3] [float] NOT NULL,
    [vfees4] [float] NOT NULL,
    [vfees5] [float] NOT NULL,
    [vfees6] [int] NULL,
    [feeduedate] [date] NULL,
    [totalvfees] [float] NOT NULL,
    [totalvfeespaid] [float] NOT NULL,
    [vfeesremaining] [float] NOT NULL,
    [vfee7] [float] NOT NULL,
    [vfee8] [float] NOT NULL,
    [vfee9] [float] NOT NULL,
    [vfee10] [float] NOT NULL,
    [vfee11] [float] NOT NULL,
    [vfee12] [float] NULL,
    [vfee13] [float] NULL,
    [vfee14] [float] NULL,
    [vfee15] [float] NULL,
    [vfee16] [float] NULL
) ON [PRIMARY]

    INSERT [dbo].[fei] ([violatorsName], [violationNumber], [violationDate]) VALUES (N'Nostra A', N'3244', CAST(0xE63C0B00 AS Date))
INSERT [dbo].[fei] ([violatorsName], [violationNumber],  [violationDate]) VALUES (N'Nuestra B', N'408', CAST(0xE53C0B00 AS Date))
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros A', N'41', 1917.71, 0, 898.3, 10.870000, 0, 1906, CAST(0x353B0B00 AS Date), 9948, 9773, 7867, 1429.5, 9296.5, 401.53999999999996, 9371, 2826.88, 20782, 28875.31, 222059.4, 0, 0)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'211', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, CAST(0x2D3B0B00 AS Date), 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'211', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, CAST(0x2D3B0B00 AS Date), 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'311', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170101', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'811', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170103', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'11111', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170301', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'77711', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170301', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)



  SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'
AND fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName)


<strong>EDIT</strong><br>
    SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE ((fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
OR (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'))
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName)
sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Quando você tem um OUTER JOIN, qualquer WHEREcláusula contra qualquer tabela externa a transforma em uma tabela interna . Ou seja, apenas as linhas em que esse predicado pode ser avaliado passarão. Efetivamente, uma WHEREcláusula em um OUTER JOINfaz com que um INNER JOIN.

    Eu tentaria:

    AND ((fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
  OR  (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'))

    Ou:

    WHERE (fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
  OR  (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330')
    • 9

  2. Outra maneira de obter os mesmos resultados - mas provavelmente com um plano de execução diferente - é obter as linhas desejadas de cada tabela separadamente e depois FULL JOINelas:

    FROM 
    ( SELECT * FROM fei 
      WHERE ViolationDate >= '20170601'
        AND ViolationDate <= '20170606'
    ) AS fe
  FULL OUTER JOIN 
    ( SELECT * FROM fji 
      WHERE feeduedate >= '20160301' 
        AND feeduedate <= '20160330'
    ) AS fj 
  ON 
    fj.violatorsName = fe.violatorsName
-- no WHERE

    Testado em dbfiddle.uk .

    SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE (fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'
 OR fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'
) 
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName);
GO

    (Sem nome de coluna) | TotalSW | TotalSNT | taxas totais | taxa calculada | Taxa de administração | SecFee
:--------------- | ------: | -------: | ---------: | -------------: | -------: | ------:
Nosotros B | 0 | 2 | 12384 | 5576 | 0 | 9713,62
Nossa B | 1 | 0 |       nulo | 0 | 0 | 0

Aviso: O valor nulo é eliminado por um agregado ou outra operação SET.

    SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM 
    (select * from fei WHERE ViolationDate >= '20170601' AND ViolationDate <= '20170606') fe
FULL OUTER JOIN 
    (select * from fji WHERE feeduedate >= '20160301' AND feeduedate <='20160330') fj 
  ON fj.violatorsName =fe.violatorsName
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName);
GO

    (Sem nome de coluna) | TotalSW | TotalSNT | taxas totais | taxa calculada | Taxa de administração | SecFee
:--------------- | ------: | -------: | ---------: | -------------: | -------: | ------:
Nosotros B | 0 | 2 | 12384 | 5576 | 0 | 9713,62
Nossa B | 1 | 0 |       nulo | 0 | 0 | 0

Aviso: O valor nulo é eliminado por um agregado ou outra operação SET.
    • 4

relate perguntas