时间戳

首页 > 解决方案 > SQL 使用两个带有 JOIN 的查询

sql - SQL 使用两个带有 JOIN 的查询

问题描述

我对 SQL 相当陌生。我试图使用两个查询并将它们连接在一起以获得我想要的结果：

select 
       A.[Article:Link]
      ,A.[ArtNo]
      ,A.[GenericArticle:Link]
      ,A.[Vehicle:Link]
      ,A.[LinkingTargetNo]
      ,Q1.[Name]
FROM [HellaPim].[dbo].[TecDoc.ArticleLinkage.Linkages] A WITH (NOLOCK) 
LEFT OUTER JOIN 
(SELECT 
       base.[TecDocNo], 
       base.[VehicleType],
       base.[TypeNo],
       T3.[Manufacturer:Link], 
       T24.[Name], 
       T1.[Designation], 
       T1.[LongDesignation], 
       T2.[DescriptionNo], 
       base.[Model:Link], 
       T4.[Designation], 
       base.[SortNo], 
       base.[ConstructionYearFrom], 
       base.[ConstructionYearTo], 
       base.[KW], 
       base.[PS], 
       base.[CcmTaxation], 
       base.[CcmTechnical], 
       base.[CapacityLitres], 
       base.[NumCylinders], 
       base.[NumDoors], 
       base.[FuelTankCapacity], 
       base.[MainVoltage], 
       base.[ABS], 
       base.[ASR], 
       base.[EngineType:Link], 
       T6.[Description <4>], 
       base.[FuelMixtureFormation:Link], 
       T8.[Description <4>], 
       base.[DriveType:Link], 
       T10.[Description <4>],  
       base.[BrakeType:Link], 
       T12.[Description <4>], 
       base.[BrakeSystem:Link], 
       T14.[Description <4>], 
       base.[NumValves], 
       base.[FuelType:Link], 
       T16.[Description <4>], 
       base.[CatalystConverterType:Link], 
       T18.[Description <4>], 
       base.[TransmissionType:Link], 
       T20.[Description <4>], 
       base.[BodyType:Link], 
       T22.[Description <4>]
FROM [HellaPim].[dbo].[TecDoc.LinkingTargets.Types] base WITH (NOLOCK)

  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models] T3 WITH (NOLOCK) 
               ON T3.[ModelNo] = [base].[Model:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers] T23 WITH (NOLOCK) 
               ON T23.[ManufacturerNo] = [T3].[Manufacturer:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers <TecDoc.GeneralData.Countries>] T24 WITH (NOLOCK) 
               ON T24.[:Id] = [T23].[:Id] 
               AND T24.[:TecDoc.GeneralData.Countries_Id] = 'A98E18B0-1401-4104-9EF9-827497E6407F'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Types <TecDoc.GeneralData.Countries>] T1 WITH (NOLOCK) 
               ON T1.[:Id] = [base].[:Id] 
               AND T1.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.CouLngDescriptions] T2 WITH (NOLOCK) 
               ON T2.[DescriptionNo] = [base].[Description:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models <TecDoc.GeneralData.Countries>] T4 WITH (NOLOCK) 
               ON T4.[:Id] = [T3].[:Id] 
               AND T4.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T5 WITH (NOLOCK) 
               ON T5.[KeyValueNo] = [base].[EngineType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T6 WITH (NOLOCK) 
               ON T6.[DescriptionNo] = [T5].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T7 WITH (NOLOCK) 
               ON T7.[KeyValueNo] = [base].[FuelMixtureFormation:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T8 WITH (NOLOCK) 
               ON T8.[DescriptionNo] = [T7].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T9 WITH (NOLOCK) 
               ON T9.[KeyValueNo] = [base].[DriveType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T10 WITH (NOLOCK) 
               ON T10.[DescriptionNo] = [T9].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T11 WITH (NOLOCK) 
               ON T11.[KeyValueNo] = [base].[BrakeType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T12 WITH (NOLOCK) 
               ON T12.[DescriptionNo] = [T11].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T13 WITH (NOLOCK) 
               ON T13.[KeyValueNo] = [base].[BrakeSystem:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T14 WITH (NOLOCK) 
               ON T14.[DescriptionNo] = [T13].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T15 WITH (NOLOCK) 
               ON T15.[KeyValueNo] = [base].[FuelType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T16 WITH (NOLOCK) 
               ON T16.[DescriptionNo] = [T15].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T17 WITH (NOLOCK) 
               ON T17.[KeyValueNo] = [base].[CatalystConverterType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T18 WITH (NOLOCK) 
               ON T18.[DescriptionNo] = [T17].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T19 WITH (NOLOCK) 
               ON T19.[KeyValueNo] = [base].[TransmissionType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T20 WITH (NOLOCK) 
               ON T20.[DescriptionNo] = [T19].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T21 WITH (NOLOCK) 
               ON T21.[KeyValueNo] = [base].[BodyType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T22 WITH (NOLOCK) 
               ON T22.[DescriptionNo] = [T21].[Name:Link]
) as Q1 on A.[LinkingTargetNo] = Q1.[TypeNo]

我的错误是

消息 8156，级别 16，状态 1，第 107 行 为“Q1”多次指定了“指定”列。

这两个查询单独工作得很好。问题是当我将它们结合在一起时。如您所见，我在 [Designation] 中使用管道两次，但一次来自 T1，一次来自 T4。

我错过了什么？

标签： sqlsql-servertsql

解决方案

您可以在下面尝试 - 您有两个名称列，一个是 T1.[Designation]，另一个是 T4.[Designation] - 一个必须重命名

select 
       A.[Article:Link]
      ,A.[ArtNo]
      ,A.[GenericArticle:Link]
      ,A.[Vehicle:Link]
      ,A.[LinkingTargetNo]
      ,Q1.[Name]
FROM [HellaPim].[dbo].[TecDoc.ArticleLinkage.Linkages] A WITH (NOLOCK) 
LEFT OUTER JOIN 
(SELECT 
       base.[TecDocNo], 
       base.[VehicleType],
       base.[TypeNo],
       T3.[Manufacturer:Link], 
       T24.[Name], 
       T1.[Designation] as designation1, 
       T1.[LongDesignation], 
       T2.[DescriptionNo], 
       base.[Model:Link], 
       T4.[Designation], 
       base.[SortNo], 
       base.[ConstructionYearFrom], 
       base.[ConstructionYearTo], 
       base.[KW], 
       base.[PS], 
       base.[CcmTaxation], 
       base.[CcmTechnical], 
       base.[CapacityLitres], 
       base.[NumCylinders], 
       base.[NumDoors], 
       base.[FuelTankCapacity], 
       base.[MainVoltage], 
       base.[ABS], 
       base.[ASR], 
       base.[EngineType:Link], 
       T6.[Description <4>], 
       base.[FuelMixtureFormation:Link], 
       T8.[Description <4>], 
       base.[DriveType:Link], 
       T10.[Description <4>],  
       base.[BrakeType:Link], 
       T12.[Description <4>], 
       base.[BrakeSystem:Link], 
       T14.[Description <4>], 
       base.[NumValves], 
       base.[FuelType:Link], 
       T16.[Description <4>], 
       base.[CatalystConverterType:Link], 
       T18.[Description <4>], 
       base.[TransmissionType:Link], 
       T20.[Description <4>], 
       base.[BodyType:Link], 
       T22.[Description <4>]
FROM [HellaPim].[dbo].[TecDoc.LinkingTargets.Types] base WITH (NOLOCK)

  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models] T3 WITH (NOLOCK) 
               ON T3.[ModelNo] = [base].[Model:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers] T23 WITH (NOLOCK) 
               ON T23.[ManufacturerNo] = [T3].[Manufacturer:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.Manufacturers <TecDoc.GeneralData.Countries>] T24 WITH (NOLOCK) 
               ON T24.[:Id] = [T23].[:Id] 
               AND T24.[:TecDoc.GeneralData.Countries_Id] = 'A98E18B0-1401-4104-9EF9-827497E6407F'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Types <TecDoc.GeneralData.Countries>] T1 WITH (NOLOCK) 
               ON T1.[:Id] = [base].[:Id] 
               AND T1.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.CouLngDescriptions] T2 WITH (NOLOCK) 
               ON T2.[DescriptionNo] = [base].[Description:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.LinkingTargets.Models <TecDoc.GeneralData.Countries>] T4 WITH (NOLOCK) 
               ON T4.[:Id] = [T3].[:Id] 
               AND T4.[:TecDoc.GeneralData.Countries_Id] = 'D3ECF38D-A5D3-4A46-BED1-24853E25DFC8'
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T5 WITH (NOLOCK) 
               ON T5.[KeyValueNo] = [base].[EngineType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T6 WITH (NOLOCK) 
               ON T6.[DescriptionNo] = [T5].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T7 WITH (NOLOCK) 
               ON T7.[KeyValueNo] = [base].[FuelMixtureFormation:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T8 WITH (NOLOCK) 
               ON T8.[DescriptionNo] = [T7].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T9 WITH (NOLOCK) 
               ON T9.[KeyValueNo] = [base].[DriveType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T10 WITH (NOLOCK) 
               ON T10.[DescriptionNo] = [T9].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T11 WITH (NOLOCK) 
               ON T11.[KeyValueNo] = [base].[BrakeType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T12 WITH (NOLOCK) 
               ON T12.[DescriptionNo] = [T11].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T13 WITH (NOLOCK) 
               ON T13.[KeyValueNo] = [base].[BrakeSystem:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T14 WITH (NOLOCK) 
               ON T14.[DescriptionNo] = [T13].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T15 WITH (NOLOCK) 
               ON T15.[KeyValueNo] = [base].[FuelType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T16 WITH (NOLOCK) 
               ON T16.[DescriptionNo] = [T15].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T17 WITH (NOLOCK) 
               ON T17.[KeyValueNo] = [base].[CatalystConverterType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T18 WITH (NOLOCK) 
               ON T18.[DescriptionNo] = [T17].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T19 WITH (NOLOCK) 
               ON T19.[KeyValueNo] = [base].[TransmissionType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T20 WITH (NOLOCK) 
               ON T20.[DescriptionNo] = [T19].[Name:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.KeyValues] T21 WITH (NOLOCK) 
               ON T21.[KeyValueNo] = [base].[BodyType:Link]
  LEFT OUTER JOIN [HellaPim].[dbo].[TecDoc.GeneralData.LngDescriptions] T22 WITH (NOLOCK) 
               ON T22.[DescriptionNo] = [T21].[Name:Link]
) as Q1 on A.[LinkingTargetNo] = Q1.[TypeNo]

推荐阅读