Page 1 of 1

query help

Posted: Sat Sep 05, 2009 2:32 am
by itsmani1
Hi,

My database table structure is:

PK_ID, category,parentId

I wanted to select parentId, PK_ID, category where PK_ID=9 and then category and PK_ID where parentId = PK_ID

Please help!

Thanks.

Re: query help

Posted: Sat Sep 05, 2009 6:58 am
by jackpf
Sorry, didn't read your post properly.

Re: query help

Posted: Sat Sep 05, 2009 12:55 pm
by Darhazer
To do this (select the record and it's parent) you need a self-join

[sql]SELECT c.PK_ID AS ChildID, c.category AS ChildCategory, p.PK_ID AS ParentID, p.category AS ParentCategoryFROM tableName c LEFT JOIN tableName p (ON c.parentid = p.PK_ID)WHERE c.PK_ID = x[/sql]

Left join is used in case the record have no parent (e.g. it's at the root level)