Select rows based on column condition SQL Server-CodePudding

I'm trying to retrieve a list of ResourceGroupName from my ResourceGroup table. The tricky part to me is, GroupsId is subset of ResourceGroupId (primary key for resource group), which means I want to be able to select all the ResourceGroupName if my condition meets ResourceGroupName = 'A30_1RecourceGrp' because this resource group has 2 subsets which are GroupId = '0014e68000000192' and GroupId = '0014e6800000001b' which is the 2 rows shown below.

This is the table that I need to select from:

And the result I wanted to achieve is

MY SQL Server knowledge is very limited and I couldn't find a way to write it, I'm stuck at the SQL below and it only shows 1 result.

SELECT * 
FROM ResourceGroup rg
LEFT JOIN ResourceGroupGroups rgg ON rgg.ResourceGroupId = rg.ResourceGroupId
WHERE rg.ResourceGroupId = '0014e68000000002'

Any help is much appreciated. Thank you!

Update :

ResourceGroup table

ResourceGroupGroups table

CodePudding user response：

You could try using distinct

SELECT distinct rrg.ResourceGroupName 
from ResourceGroup rg
LEFT JOIN ResourceGroupGroups rgg ON rgg.ResourceGroupId = rg.ResourceGroupId
WHERE rg.ResourceGroupId = '0014e68000000002'

CodePudding user response：

I think you made wrong join, It should be like below:

SELECT ResourceGroupName  
from ResourceGroupGroups rgg 
LEFT JOIN ResourceGroup rg ON rgg.ResourceGroupId = rg.GroupsId and rg.ResourceGroupId = '0014e68000000002'