| sql parent and child categories order with hierarchy
sql parent and child categories order with hierarchy |
drooh
msg:4334867 | 5:28 pm on Jul 4, 2011 (gmt 0) |
Ok, trying to expand my knowledge of SQL JOIN
Here is my DB
id parent_id type
1 0 Arts & Entertainment
2 1 Arcades & Amusements
3 1 Concerts
4 1 Carnivals
14 1 Fairs & Festivals
16 0 Automotive
17 16 Accessories
18 16 Dealers & Brokers
19 16 Auto Repair
20 1 Children's & Family Entertainment
I would like to be able to output a list in order like this
Arts & Entertainment
- Arcades & Amusements
- Concerts
- Carnivals
- Children's & Family Entertainment
- Fairs & Festivals
Automotive
- Accessories
- Dealers & Brokers
- Auto Repair
This easy solution for me would be to do something like this
$sql = "SELECT * FROM `advertiser_types` WHERE `parent_id` = 0 ORDER BY `type`";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
print $row['type'].'<br />';
$sql1 = "SELECT * FROM `advertiser_types` WHERE `parent_id` = ".$row['id']." ORDER BY `type`";
$result1 = mysql_query($sql1);
while($row1 = mysql_fetch_array($result1)){
print '- '.$row1['type'].'<br />';
}
} |
|
But Ive been trying to wrap my head around self Joins, the closest thing ive got it this
$sql = "
SELECT parent.type AS 'p_type', child.type AS 'c_type', child.id AS 'c_id'
FROM advertiser_types AS child LEFT OUTER JOIN advertiser_types AS parent
ON child.parent_id = parent.id
";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
if($row['p_type']){
echo $row['p_type'].' - ';
}
echo $row['c_type'].'('.$row['c_id'].')<br />';
} |
|
But I dont completely understand why it works, and also Im not sure how I would get it to sort the alphabetically
|
arms
msg:4362845 | 1:14 pm on Sep 15, 2011 (gmt 0) |
Try this as a 2 columkn result
SELECTtype, Spare
FROM
(SELECTDISTINCT advertiser_types.type,' ' AS Spare, advertiser_types.id, 1 as ord
FROMadvertiser_types INNER JOIN advertiser_types AS advertiser_types_1
ONadvertiser_types.id = advertiser_types_1.parent_id
UNION ALL
SELECT'-' AS Spare, advertiser_types_1.type, advertiser_types_1.parent_id, 2 as ord
FROMadvertiser_types INNER JOIN advertiser_types AS advertiser_types_1
ONadvertiser_types.id = advertiser_types_1.parent_id
) s
ORDER BY id, ord
(Did this in MSSqlserver so syntax may vary slightly)
|
|
|
