retrieving same column twice from a table

I have a table named "address", which has:

开发者_开发技巧

• id
• title
• parent_id

...fields.

In the title column, the name of regions and districts are inserted. The regions have parent_id value of zero, and parent_id of the districts are id of the regions.

I want a query which display regions in one column and it's respective districts in another column.

---

You could join the table onto itself like this:

SELECT
   R.Title AS Region,
   D.Title AS District
FROM
   address R
INNER JOIN
   address D
   ON
   D.parent_id = R.id
   AND
   D.parent_id > 0
WHERE
   R.parent_id = 0

However your table structure is a bit odd, I would do some reading on database normalization.

---

Use something like the following:

Select r.Region, s.State 
from
    (Select id, title as Region from address where parent_id=0) as r,
    (Select id, title as State, parent_id from address where parent_id>0) as s
where s.parent_id = r.id

Its an ANSI SQL. You can further tune it for your need.