query help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

query help

Post 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.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: query help

Post by jackpf »

Sorry, didn't read your post properly.
Last edited by jackpf on Sat Sep 05, 2009 1:21 pm, edited 1 time in total.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: query help

Post 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)
Post Reply