Welcome to WebmasterWorld Guest from 54.147.236.192

Forum Moderators: open

Message Too Old, No Replies

need help on displaying categories/sub categories

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

New User

joined:May 25, 2013
posts: 7
votes: 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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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
12:27 am on May 28, 2013 (gmt 0)

New User

joined:May 25, 2013
posts: 7
votes: 0


thanks