referencing 2 rows in same table with sql statement

[stinkybee]

I need to get "name" from the table where id = parent_id of an id I pass to the sql statement.

example table

id name parent_id

1 I NEED THIS 0
9 test 1

So if I pass the variable 9 to the sql statement it will need to pull out "I NEED THIS"

I hope this makes sense.

 

[TonyGroves]

You need to do a self-join, in other words join the table to itself with 2 different aliases.

 

[stinkybee]

Thanks for the reply, that worked. A more complex question now. How do I do a condition in the sql statement.

For exmaple, I need to get "name" from the table where id = parent_id of an id I pass to the sql statement only if the parent_id does not = 0. If it does = 0 then I need the "name" from the row with the id that I originally passed to the statement.

id name parent_id

1 I NEED THIS 0
9 test 1

In the above example table if I pass the id 1 or 9 then I need the name "I NEED THIS"

Again, I hope this makes sense.

 

[TonyGroves]

You could use an IF (or CASE) expression to select either of the two names depending on the value of parent_id.