CODEWITHSUNDEEP
sqljoin
user949000
user949000

Reputation: 323

sql self join, select categories and sub-categories from one table

I'm trying to create a forum with categories and subcategories in one table and I want to select all cat and subcat in one query but somehow the query is giving me error message like this

Unknown column 'fcat.fcat_id' in 'on clause'

though I can't figure out what's wrong. Here is the query by the way:

SELECT fcat.id          fcat_id, 
       fcat.name        fcat_name, 
       fcat.description fcat_description, 
       fcat.order       fcat_order, 
       fcat.url         fcat_url, 
       fcat.visibility  fcat_visibility, 
       fcat.parent      fcat_parent, 
       fcat.created_at  fcat_createdat, 
       fcat.is_active   fcat_isactive, 
       fsub.id          fsub_id, 
       fsub.name        fsub_name, 
       fsub.description fsub_description, 
       fsub.order       fsub_order, 
       fsub.url         fsub_url, 
       fsub.visibility  fsub_visibility, 
       fsub.parent      fsub_parent, 
       fsub.created_at  fsub_createdat, 
       fsub.is_active   fsub_isactive 
FROM   forum_categories fcat 
       LEFT OUTER JOIN forum_categories fsub 
                    ON fcat.fcat_id = fsub.fsub_parent
ORDER  BY fcat.fcat_id;

With that error, I can't even test if the query result is what I expect it to be. Please help. (^__^')

PS: If you could help me simplify the statement, that would help me a lot. Thanks in advance. :)

Upvotes: 0

Views: 1859

Answers (1)

sarwar026
sarwar026

Reputation: 3821

Replace 

ON fcat.fcat_id = fsub.fsub_parent

with 

ON fcat.id = fsub.fsub_parent

QUERY:

SELECT fcat.id          fcat_id, 
       ...
FROM   forum_categories fcat 
       LEFT OUTER JOIN forum_categories fsub 
                    ON fcat.id = fsub.fsub_parent
ORDER  BY fcat.id;

Upvotes: 1

Related Questions