Welcome to WebmasterWorld Guest from 54.159.214.27

Forum Moderators: open

need help on displaying categories/sub categories

   
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.
11:04 pm on May 27, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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
12:27 am on May 28, 2013 (gmt 0)



thanks
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month