'SQL Query with INNER Join is returning multiple duplicate values

A view and a table, vwGrossEnergyYields and tblTurbineLayouts, are being combined with an inner join. Each table/view contains a field ProjectID and IsLive. Querying each by an example ProjectID and IsLive = 1 returns one row for each table/view:

SELECT [TurbineLayoutProjectID]
    ,[TurbineLayoutNumber]
    ,[Number]
    ,[ProjectID]
    
  FROM [GrossEnergy].[dbo].[vwGrossEnergyYields]
  
 WHERE [IsLive] = 1
 AND [ProjectID] IN
  (
    '2835'
  )

gives:

TurbineLayoutProjectID  TurbineLayoutNumber Number  ProjectID
2835    52  170 2835

and

SELECT  [Number]
    ,[ProjectID]
    
FROM [TurbineLayout].[dbo].[tblTurbineLayouts]
  
WHERE [IsLive] = 1
AND [ProjectID] IN
  (
    '2835'
  )

gives

Number  ProjectID
52  2835

A query is written, using INNER join, joining on TurbineLayoutNumber, intending to return only one row:

SELECT 
      [vwGrossEnergyYields].[TurbineLayoutProjectID] As TurbineLayoutProjID
      ,[vwGrossEnergyYields].[ProjectID]  As YieldProjID
      ,[vwGrossEnergyYields].[TurbineLayoutNumber]
      ,[vwGrossEnergyYields].[Number] As YieldNumber
  FROM [GrossEnergy].[dbo].[vwGrossEnergyYields]
  
  INNER JOIN [TurbineLayout].[dbo].[tblTurbineLayouts]
  ON [vwGrossEnergyYields].[TurbineLayoutNumber] = [tblTurbineLayouts].[Number]   
 
  WHERE [vwGrossEnergyYields].[IsLive] = 1
  AND [tblTurbineLayouts].[IsLive] = 1
  AND
  [vwGrossEnergyYields].[ProjectID] IN
  (
    '2835'
  )

This returns five rows:

TurbineLayoutProjID YieldProjID TurbineLayoutNumber YieldNumber
2835    2835    52  170
2835    2835    52  170
2835    2835    52  170
2835    2835    52  170
2835    2835    52  170

What is the error in the query that is causing it to return five rows instead of one?

sqlsql-serverinner-join


Solution 1:[1]

The query is joined to a one to many relationship meaning - [vwGrossEnergyYields].[TurbineLayoutNumber] = [tblTurbineLayouts].[Number] - this join has multiple values in one of the tables - try joining with a different field or put the distinct keyword in your query for example:

SELECT DISTINCT
  [vwGrossEnergyYields].[TurbineLayoutProjectID] As TurbineLayoutProjID
  ,[vwGrossEnergyYields].[ProjectID]  As YieldProjID
  ,[vwGrossEnergyYields].[TurbineLayoutNumber]
  ,[vwGrossEnergyYields].[Number] As YieldNumber
 FROM [GrossEnergy].[dbo].[vwGrossEnergyYields]

 INNER JOIN [TurbineLayout].[dbo].[tblTurbineLayouts]
 ON [vwGrossEnergyYields].[TurbineLayoutNumber] = [tblTurbineLayouts].[Number]   

 WHERE [vwGrossEnergyYields].[IsLive] = 1
 AND [tblTurbineLayouts].[IsLive] = 1
 AND
      [vwGrossEnergyYields].[ProjectID] IN
 (
     '2835'
 )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Johanc

Related Questions