equality checks between 2 fields using SQL

Question

I am trying to find the parent categories.

Therefore i need to write,

where CategoryID HAS a ParentCategoryID of 0

CategoryID could be 30 but if it has a ParentCategoryID of 0 then you know its the parent category.

This is my SQL so far:

SELECT        CategoryID, ParentCategoryID, Name, Published, Deleted, PictureID
FROM          Nop_Category
WHERE         (Deleted = 0) 
AND           (Published = 1) 
AND           (CategoryID = ParentCategoryID = 0)

Chris Gessler · Accepted Answer · 2012-07-08T16:42:28.583

To perform equality checks against two fields, use the AND operator and specify the fieldname twice.

SELECT        CategoryID, ParentCategoryID, Name, Published, Deleted, PictureID 
FROM          Nop_Category 
WHERE         (Deleted = 0)  
AND           (Published = 1)  
AND           (CategoryID = ParentCategoryID AND ParentCategoryID = 0)

But you could also write it like so and achieve the same results:

SELECT        CategoryID, ParentCategoryID, Name, Published, Deleted, PictureID 
FROM          Nop_Category 
WHERE         (Deleted = 0)  
AND           (Published = 1)  
AND           (CategoryID = 0 AND ParentCategoryID = 0)

However, in your question, you mentioned that CategoryID could be 30, so your query won't work. You'll likely want to leave out the CategoryID or specify a specific categoryId through a parameter:

SELECT        CategoryID, ParentCategoryID, Name, Published, Deleted, PictureID, 
FROM          Nop_Category 
WHERE         (Deleted = 0)  
AND           (Published = 1)  
AND           (CategoryID = @categoryID AND ParentCategoryID = 0)

EDIT:

so if the categoryID is the same as the CetegoryParentID i know its a child.

Generally when I do self-related tables, I use NULL for the ParentId which tells me that the current row is the parent. If you're using 0 for null, then a record with a CategoryId of 30 and a ParentCategoryId of 30 means it's neather a child nor a parent.

ID  PID  Value
0   0    Top Level - must exist for referential integrity
1   0    Child of "Top Level"
2   0    Another child of "Top Level"
3   1    Child of "Child of Top Level"

In this scenerio, you can only have 1 top level category, ALL others will be children (even though you consider a ParentCategoryId of 0 a parent, it still must live under CategoryId 0)

Using NULL

ID  PID  Value
1   Null Top Level cat 1
2   Null Top Level cat 2
3   1    Child of "Top Level cat 1"
4   2    Child of "Top Level cat 2"

In this scenerio, I can easily find ALL top level categories

SELECT * FROM dbo.Category WHERE pid IS NULL

Or, if I want the top category for a specific category

SELECT * FROM dbo.Category WHERE CategoryId = 1 AND ParentCategoryId is null

And my referential integrity is completely in tact.

To locate immediate children of a parent, simply pass in the categoryid of the parent you're looking for:

SELECT * FROM dbo.Category WHERE ParentCategoryId = 1

While syntactically correct, it doesn't make much sense - both `CategoryID` and `ParentCategoryID` would be 0. — Lucero, Jul 08 '12 at 15:51
Correct... you could say CatId = 0 and ParentCatId = 0 and achieve the same results. — Chris Gessler, Jul 08 '12 at 15:58
how do i check for a child? eg, CategoryID is the same as ParentCategoryID — SOLDIER-OF-FORTUNE, Jul 08 '12 at 16:10
@Super1 - the CategoryID and the ParentCategoryID should never be the same, otherwise it breaks the hierarchy. A child will ALWAYS have a parentId that is not the same as it's own Id, that's what makes it a child. — Chris Gessler, Jul 08 '12 at 16:38
Thanks for taking time to help me. I am just trying to learn on a Sunday evening! Posted an extension to this question here: http://stackoverflow.com/questions/11384940/display-sub-categories-using-sql — SOLDIER-OF-FORTUNE, Jul 08 '12 at 17:00

score 0 · Answer 2 · answered Jul 08 '12 at 15:51

0

SELECT        CategoryID, ParentCategoryID, Name, Published, Deleted, PictureID
FROM          Nop_Category
WHERE         Deleted = 0
AND           Published = 1
AND           ParentCategoryID = 0

answered Jul 08 '12 at 15:51

Randy

16,480
1
37
55

You answered my question thanks. But how do I write, `CategoryID == ParentCategoryID` – SOLDIER-OF-FORTUNE Jul 08 '12 at 15:56
Doesn't take into account CategoryID – Chris Gessler Jul 08 '12 at 15:58
so if the categoryID is the same as the CetegoryParentID i know its a child. – SOLDIER-OF-FORTUNE Jul 08 '12 at 16:01

equality checks between 2 fields using SQL

2 Answers2