MySQL Forums
Forum List  »  Newbie

Selecting Rows while using values from these rows in the where clause
Posted by: Simon Benner
Date: December 24, 2008 07:00AM

Hi everyone,

I have a table which includes 3 columns:

id: just an id
name: a name
parent: the id of the row which this row is a child of.

In my example I want to select the rows with the id 5, it's parent, and the parent's parent. I found a syntax that workes in this case:

SELECT `name`,`id` FROM `locations` WHERE `id`='5' or `id`=(SELECT `parent` FROM `locations` WHERE `id`='5') or `id`=(SELECT `parent` FROM `locations` WHERE `id`=(SELECT `parent` FROM `locations` WHERE `id`='5'))

However my question now is if this syntax could be shortened while still working, and also if there is a way to allow for rows to be selected that do not have a parent( value 0 in my table) or that only have a parent but no grandparent.

thanks a lot

simon



Edited 1 time(s). Last edit at 12/24/2008 07:01AM by Simon Benner.

Options: ReplyQuote


Subject
Written By
Posted
Selecting Rows while using values from these rows in the where clause
December 24, 2008 07:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.