I need to add validation on category creation.
parentId should be valid if supplied
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:
- I cannot provide an invalid parentId
- I cannot create a category with name: CatA where parentId = null
- 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:
- The provided parentId is invalid
- 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?