Welcome to WebmasterWorld Guest from 54.162.240.235

Forum Moderators: open

Message Too Old, No Replies

sql parent and child categories order with hierarchy

sql parent and child categories order with hierarchy

     
5:28 pm on Jul 4, 2011 (gmt 0)

5+ Year Member



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
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)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month