SQL Query with INNER Join is returning multiple duplicate values-CodePudding

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?

CodePudding user response：

As I mentioned in the comments, presumably you have a One-To-Many relationship, so you get 1 row for each related row.

As you aren't returning any data from the table tblTurbineLayouts then it seems an EXISTS would be a better solution:

SELECT GEY.TurbineLayoutProjectID AS TurbineLayoutProjID,
       GEY.ProjectID AS YieldProjID,
       GEY.TurbineLayoutNumber,
       GEY.Number AS YieldNumber
FROM GrossEnergy.dbo.vwGrossEnergyYields GEY
WHERE GEY.IsLive = 1
  AND GEY.ProjectID = '2835' --Change to =. Also should the value be an int, rather than a varchar(4)?
  AND EXISTS (SELECT 1
              FROM TurbineLayout.dbo.tblTurbineLayouts TL
              WHERE TL.Number = GEY.TurbineLayoutNumber
                AND TL.IsLive = 1)

I also make some QoL changes, such as aliasing.

CodePudding user response：

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'
 )

CodePudding user response：

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

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' )