November 13, 2016 at 8:46 pm
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.
November 14, 2016 at 1:06 am
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
;
November 14, 2016 at 6:28 am
Thank you. 🙂
November 14, 2016 at 6:48 am
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