CODEWITHSUNDEEP
sqlsql-server-2008
SOLDIER-OF-FORTUNE
SOLDIER-OF-FORTUNE

Reputation: 1654

equality checks between 2 fields using SQL

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)

Upvotes: 0

Views: 190

Answers (2)

Chris Gessler
Chris Gessler

Reputation: 23123

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

Upvotes: 1

Randy
Randy

Reputation: 16677

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

Upvotes: 0

Related Questions