[SOLVED] Query help

[Brian W]

Can't seem to wrap my head around this query.

I have a table of locations. Each location has a parent location represented by the parents unique ID on the same table. An example would be

Miami > Florida > United States > Northern Hemisphere > Earth > Milky Way [top level and does not have a parent]

ID | NAME | PARENT

2  | Florida | 18

5  | Miami | 2

18 | United States | 58

 

What I want to do is display a the table like this

Name | Parent

Miami | Florida

Phoenix | Arizona

 

How do I join the Name of the parent on locations.Parent = locations.ID

 

any input appreciated

[premiso]

I think you have to do a multiple query with a recursive function. I do not think it is possible with 1 query. Or just do one query and use PHP to traverse the array returned and order it like you want.

 

Here is an example of what I used to accomplish this:

 

<?php
/*
* My attempt at a category script.
*/

/*
create table categories (
catid INT(11) NOT NULL auto_increment,
parentid INT(11) NOT NULL default '0',
catname varchar(50) NOT NULL,
catseoname varchar(50) NOT NULL,
disporder INT(3) NOT NULL default '0',
primary key(catid)
);
*/

mysql_connect("localhost", "root", "");
mysql_select_db("cat");

/*
my test data
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('0', 'Test1', 'test1', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('1', 'Test1-1', 'test1-1', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('1', 'Test1-2', 'test1-2', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('5', 'Test2-1', 'test2-1', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('0', 'Test2', 'test2', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('5', 'Test2-3', 'test2-2', '2');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('6', 'Test2-2-2', 'test2-2-2', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('6', 'Test2-2-1', 'test2-2-1', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('5', 'Test2-3', 'test2-2', '2');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('7', 'Test2-2-2-1', 'test2-2-2-1', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('7', 'Test2-2-2-2', 'test2-2-2-2', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('7', 'Test2-2-2-3', 'test2-2-2-3', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('3', 'Test1-2-1', 'test1-2-1', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('3', 'Test1-2-2', 'test1-2-2', '0');

*/

function retrieveCategoryList($parentid=false) {
if ($parentid === false) {
	$query = mysql_query("SELECT catid, parentid, catname, catseoname, disporder FROM categories WHERE parentid = 0 ORDER BY disporder, catname");
}else {
	$query = mysql_query("SELECT catid, parentid, catname, catseoname, disporder FROM categories WHERE parentid = " . $parentid . " ORDER BY disporder, catname");
}
$numRows = mysql_num_rows($query);

$catArray = "none";
if ($numRows > 0) {
	$catArray=array();
	while ($row = mysql_fetch_assoc($query)) {
		$catArray[$row['catname']]['id'] = $row['catid'];
		$catArray[$row['catname']]['parentid'] = $row['parentid'];
		$catArray[$row['catname']]['catseoname'] = $row['catseoname'];
		$catArray[$row['catname']]['catname'] = $row['catname'];
		$catArray[$row['catname']]['disporder'] = $row['disporder'];
		$catArray[$row['catname']]['subcats'] = retrieveCategoryList($row['catid']);
	}
}

return $catArray;
}

function displayCats($cat=false, $charDisp=0) {
if (!$cat) {
	if ($charDisp != 0) {
		$cats = retrieveCategoryList($charDisp);
	}else {
		$cats = retrieveCategoryList();
	}
	foreach ($cats as $cat) {
		displayCats($cat);
	}
}else {
	echo str_repeat("_", $charDisp) . $cat['catname'] . "<br />";
	if ($cat['subcats'] != "none") {
		foreach ($cat['subcats'] as $value) {
			displayCats($value, $charDisp+1);
		}
	}
}
}

displayCats(false, 6);

echo "<pre>";
// Lets just pull out a set of categories.
print_r(retrieveCategoryList());
?>

[revraz]

You should be able to do a Self Join

 

SELECT  TABLE.NAME, TABLE.PARENT

    FROM    TABLE FIRST, PARENT SECOND

    WHERE    SECOND.PARENT = FIRST.ID;

[xtopolis]

I don't know about what premiso posted for your entire problem...

 

But for your direct question to display a table like that, what about:

SELECT x.NAME,t.NAME FROM yourTable t
INNER JOIN yourTable x
ON(x.PARENT = t.ID)

[Brian W]

Bingo, xtopolis! That is what I was looking for. Thanks very much and thanks every one else too.

[xtopolis]

FYI, if your table gets too big, you will need to create indexes for speed-a-bility !