sql query for category heirarchy validations

I need to add validation on category creation. **CASE 1:** parentId should be valid if supplied **CASE 2:** name of sibling could not be duplicated I have this table: **(categories)** id | parentId | name -----|-----------|------ 1 | NULL | CatA 2 | 1 | CatA.1 (Note: My parent child hierarchy can go up-to nth level) Now in the above scenario what should not be allowed is: 1. I cannot provide an invalid parentId 2. I cannot create a category with name: CatA where parentId = null 3. I cannot create a category where name: CatA.1 where parentId = 1 Now I am in a nodejs so I need to return these 2 validations errors: 1) The provided parentId is invalid 2) Duplicate name detected Now I want to achieve this using a single optimized SQL query. I can use if else statements later on the base of query response. But for me it is really important that I use single query and that query should be as opptimized as possible. What I tried so far is: SELECT TOP 1 parentId, name, ( CASE WHEN name = 'CatA.2' THEN 1 ELSE 0 ) sortOrder FROM catagories WHERE parentId = 1 ORDER BY sortOrder DESC Now the issue with my query is that it doesn't cover all the scenarios. Can anyone help me with the query?