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:
- 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?