SQL complex join with One to Many relationships

  • 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 AppFabricDEVhttp://www.testapp.com10.1.1.5

    Server1Test AppFabricDEVhttp://www.testappui.com10.1.1.6

    Server2Test AppFabricDEVhttp://www.testapp.com10.1.1.5

    Server2Test AppFabricDEVhttp://www.testappui.com10.1.1.6

    Server3Test AppFabric UIDEVhttp://www.testapp.com10.1.1.5

    Server3Test AppFabric UIDEVhttp://www.testappui.com10.1.1.6

    Server4Test AppFabric UIDEVhttp://www.testapp.com10.1.1.5

    Server4Test AppFabric UIDEVhttp://www.testappui.com10.1.1.6

    Server5Test AppFabricINTNULLNULL

    server6Test AppFabricINTNULLNULL

    Any help is really appreciated. 🙂

    Thanks.

  • 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

    ;

  • Thank you. 🙂

  • raymak (11/14/2016)


    Thank you. 🙂

    You are very welcome.

    😎

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply