SQL custom column from another queried column value

Question

I have the below query which works as I need it, but I also want to add the name of the ParentCategoryID in another column which would need to custom as there is no column for ParentCategory name. The only way I can get that is by referencing the CategoryID that I retrieved from t.ParenCategoryID. CategoryID and ParentCategoryID are in the same table. Can this be accomplished a sub query?

SELECT c.ContentID
    ,c.Title
    ,t.CategoryID
    ,t.Name
   ,t.ParentCategoryID
FROM Content c
INNER JOIN ContentCategory g ON c.ContentID = g.ContentID
RIGHT JOIN Category t ON g.CategoryID = t.CategoryID
ORDER BY g.CategoryID

Thanks in advance.

Giannis Paraskevopoulos · Accepted Answer · 2014-09-25 14:53:15Z

3

SELECT c.ContentID
       ,c.Title
       ,t.CategoryID
       ,t.Name
       ,t.ParentCategoryID
       ,tc.Name AS ParentCategoryName
FROM   Content c
       INNER JOIN ContentCategory g 
           ON c.ContentID = g.ContentID
       RIGHT JOIN Category t 
           ON g.CategoryID = t.CategoryID
       LEFT JOIN Category tc 
           ON t.CategoryID = tc.ParentCategoryID
ORDER BY g.CategoryID

answered Sep 25, 2014 at 14:53

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron Bertrand Over a year ago

It's very confusing to see INNER...RIGHT...LEFT.

Andrew L. Over a year ago

Thanks. The name results for the parent category name are not matching, but I think that's a byproduct of my initial query. I did a similar query using different tables and it worked fine.

Collectives™ on Stack Overflow

SQL custom column from another queried column value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related