SQL complex join with One to Many relationships

• raymak

  Grasshopper

  Points: 12

  More actions

  November 13, 2016 at 8:46 pm

  #104306

  Hello,

  I need help writing a query. I am stuck at the problem and not sure how I can resolve that.

  I have a table called ResourceGroup and its primary key is a foreign key to other tables like ResourceGroupEnvironment and ServerResourceGroup tables. I am trying to join all the tables that has a common key from ResourceGroup, but only join ResourceGroupEnvironment and ServerResourceGroup tables with common Environment key from Environments table.

  I have attached Entity design in this post to help understand what I am trying to achieve.

  I created below query to return data from database and I am able to get data back, but I am getting duplicate records in results.

  SELECT s.ServerName, rg.Name as ResourceGroup, e.Name as Env,

  rge.EndPointURL, rge.VIP

  FROM ResourceGroup as rg

  JOIN ServersResourceGroup as srg on rg.Id = srg.ResourceGroup_id

  JOIN Servers as s on srg.Server_id = s.Id

  JOIN Environments as e on srg.Environment_id = e.Id

  LEFT JOIN ResourceGroupEnvironment as rge on srg.Environment_id = rge.Environment_id

  Here is the result I am getting from above query:

  ServerName ResourceGroup Env EndPointURL VIP

  Server1Test AppFabricDEVwww.testapp.com10.1.1.5

  Server1Test AppFabricDEVwww.testappui.com10.1.1.6

  Server2Test AppFabricDEVwww.testapp.com10.1.1.5

  Server2Test AppFabricDEVwww.testappui.com10.1.1.6

  Server3Test AppFabric UIDEVwww.testapp.com10.1.1.5

  Server3Test AppFabric UIDEVwww.testappui.com10.1.1.6

  Server4Test AppFabric UIDEVwww.testapp.com10.1.1.5

  Server4Test AppFabric UIDEVwww.testappui.com10.1.1.6

  Server5Test AppFabricINTNULLNULL

  server6Test AppFabricINTNULLNULL

  Any help is really appreciated.

  Thanks.

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  November 14, 2016 at 1:06 am

  #1912171

  At the first glance, it looks like you are missing the second join specification for the ResourceGroupEnvironment table as the duplication is coming from that table (different VIP values), see the bolded line below.

  

  SELECT

  s.ServerName

  , rg.Name AS ResourceGroup

  , e.Name AS Env

  , rge.EndPointURL

  , rge.VIP

  FROM ResourceGroup rg

  JOIN ServersResourceGroup srg

  ON rg.Id = srg.ResourceGroup_id

  JOIN Servers s

  ON srg.Server_id = s.Id

  JOIN Environments e

  ON srg.Environment_id = e.Id

  LEFT JOIN ResourceGroupEnvironment rge

  ON srg.Environment_id = rge.Environment_id

  AND rg.Id = rge.ResourceGroup_Id

  ;

• raymak

  Grasshopper

  Points: 12

  More actions

  November 14, 2016 at 6:28 am

  #1912226

  Thank you.

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  November 14, 2016 at 6:48 am

  #1912240

  raymak (11/14/2016)

  ---

  Thank you.

  You are very welcome.