mySQL self join elegantly

Question

My question is a bit dummy, but SQL has never been my cup of tea. I have a simple table with 3 columns: id, name, parent_id (referring to an id in the same table).

I just want to get the relevant results to display them in an HTML page: ID / Name / Parent Name

I can query as follows:

select id, name, parent_id from fields
union
select a.id, a.name, b.name
from fields a, fields b
where b.parent_id = a.id;

and do some tests, but i can figure out that it exists some more elegant manner.

Thx.

Answer 1

This query is best achieved by using a LEFT JOIN. This way you can still return fields which do not have a parent_id, i.e. are NULL. You'll also want to select the parent_name using an alias (parent_name in the example). This will allow you to programatically refer to the result by it's name rather than the numerical column index ($row->parent_name vs $row[2] if PHP is your language of choice).

SELECT f1.id, f1.name, f2.name parent_name
FROM fields f1
LEFT JOIN fields f2 ON f2.id = f1.parent_id

http://sqlfiddle.com/#!2/a9632/1/0

Answer 2

SELECT A.id AS ID, A.name AS Name, B.name AS Parent
FROM fields A, fields B
WHERE A.parent_id = B.id