[RESOLVED] Selecting parent name from ID in one query

[Kudose]

Hello all.

Basically I'm trying to get the name of the parent and the ID (it's natural state) in one query.

I'm not sure of the best method or how to form the proper query, unless it is a sub-select.

Should I use a sub-select?

Here is my code:

  /** get all of the categories **/
  $SQL = "SELECT c.id, c.parent, c.location, c.name";
  $SQL .= " FROM ".DB_PREFIX."categories AS c ORDER BY c.id ASC";
  $cats = $SD_DB->fetchAll($SQL);

Thanks in advance for any tips.

[Sxooter]

A join is generally better for these things.

SELECT c2.id, c1.parent, c1.location, c1.name 
FROM categories AS c2 
join categories as c1
on (c2.parent=c1.id)
where c2.name='bob'
ORDER BY c2.id ASC

[Kudose]

Thanks. 🙂