New to SQL need help with a query

Transact-SQL
1

Hi all. I have data in column level_1 and level_2. It is a simple Hierarchy so as such there should be no Level_2 values with more than one corresponding Level_1.

For example, for the level_1 'DARK', Level_2 values may be "PURPLE" and "BLUE". Similarly, for the level_1 "LIGHT" level_2 may be "PINK" and "AQUA". However, the level_2 value "PINK" should not ever fall with the level_1 'DARK', just as "PURPLE" should never be 'LIGHT'.

I am trying to create a code that finds level_2 values that fall under multiple level_1 values so they can be manually fixed. Any ideas?

I hope this makes sense to someone and sorry if I've worded incorrectly. I am very new to this.

2

Hi squidsyd,
Would level_2 value only map to 1 level_1 value? or it can map to more then 1 level_1 value?

If its a 1-to-1 mapping, you can simply do a count and group by the level_2 values having count more then 1.

Hope this helps

1 Like
3

Something like this

SELECT Level_2_Colour, COUNT(*) AS MyCount
FROM MyHierarchyTable
GROUP BY Level_2_Colour
HAVING COUNT(*) >= 2
1 Like
4

Thanks Dennisc. I am trying to find the level_2 values that map to multiple level_1 values and then manually change them so they no longer map to multiple level_1 values. I've done a terrible job of explaining it but thank you for your help