homepage Welcome to WebmasterWorld Guest from 107.21.187.131
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
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)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved