homepage Welcome to WebmasterWorld Guest from 54.204.249.184
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

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

Databases Forum

    
need help on displaying categories/sub categories
aldwin




msg:4577681
 7:24 am on May 25, 2013 (gmt 0)

i am having a bit of trouble.

i have the following tables

Categories
+--------+------------+
| ID | name |
+--------+------------+
| 1 | Accommodation |
| 2 | Advertising |
| 3 | Architects |
+--------+------------+

Classifications
+--------+--------------------------+
| ID | category_id | name |
+--------+--------------------------+
| 1 | 1 | Hotels |
| 2 | 1 | Lodge |
| 3 | 2 | Ad Agency |
| 3 | 2 | Architects Supplies |
+--------+--------------------------+

Business
+--------+--------------------------+
| ID | classification_id | name |
+--------+--------------------------+
| 1 | 1 | 123 Hotel |
| 2 | 1 | ABCLodge |
| 3 | 2 | XYZAd Agency |
+--------+--------------------------+

How can i display a side menu like this

Accommodation
- Hotels
- Lodge
Advertising
- Ad Agency


but it should only contain categories/classifications if there is an entry in the business table that uses it, meaning "Architects- architects supplies" should not appear.

Any help would be appreciated.

 

Dijkgraaf




msg:4578371
 11:04 pm on May 27, 2013 (gmt 0)

You probably want to do something along the line of

select
cat.name as CategoryName,
clas.name as ClassificationName
from Categories cat
inner join Classifications clas on clas.category_id = cat.ID
where exists (select 1 from Business bus where bus.classification_id = clas.ID)

Which will get you a result set of
CategoryName | ClassificationName
Accommodation | Hotels
Accommodation | Lodge
Advertising | Ad Agency

aldwin




msg:4578390
 12:27 am on May 28, 2013 (gmt 0)

thanks

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