retrieving same column twice from a table

Question

hello all i hav a table named address which has id, title and parent_id fields. in title column the name of regions and districts are inserted. the regions have parent_id zero and parent_id of the districts are id of the regions. i want a query which display regions in one column and its respective districts in another column. hope u guys understand what i mean.. thank u all.

Answer 1

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.

Answer 2

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.