need help on displaying categories/sub categories

Forum Moderators: open

Message Too Old, No Replies

need help on displaying categories/sub categories

aldwin

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

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

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

thanks