Query to pull document categories (with their parent), only when the category actually contains documents?

In my app a "document" is simply a table record of info. Here's the tables (simplified some)

Document Table

• DocId int PK
• Title
• Content
• DateUploaded
• etc...

DocumentCategories Table

• CatId
• ParentId
• Name
• 开发者_JS百科etc...

DocCats Table (join table)

• DocId PK
• CatId PK

With appropriate relations as well. "Documents" are put into categories by making an entry in the DocCats table (DocId of the Document, CatId of the category). Categories can have parent categories, noted by the ParentId field. If the ParentId is 0, the category is a parent category.

I'd like to pull all the categories which actually contain a document, with one caveat: I need the parent category as well.

Quick type-up, so I can't guarantee this is completely bug free, but this should be the general idea. Join Categories back to itself to get the parent name and then check for existence of the initial category in the DocCats table.

select
    cat.Name, parent.Name
from
    DocumentCategories cat
    inner join DocumentCategories parent
        on  parent.CatId = case when cat.ParentId = 0 then cat.CatId else cat.ParentId end
where
    exists (
        select 1
        from 
            DocCats dc
        where
            dc.CatId = cat.CatId)

SELECT cat.Name, parent.Name
FROM DocumentCategories AS cat
  LEFT JOIN DocumentCategories as parent ON (cat.ParentId = parent.CatId)
  LEFT JOIN (SELECT DISTINCT CatID FROM DocCats) AS doc ON (cat.CatId = doc.CatId)
WHERE (doc.CatId IS NOT NULL)