#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    8
    Rep Power
    0

    Need help on displaying categories/sub categories


    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,167
    Rep Power
    4274
    Code:
    SELECT DISTINCT
           categories.name AS category
         , classifications.name AS classification
      FROM categories
    INNER
      JOIN classifications
        ON classifications.category_id = categories.id
    INNER
      JOIN business             
        ON business.classification_id = classifications.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    8
    Rep Power
    0
    thank you very much.

    got another question, but that seems more ondata formatting and PHP, so i'm posting the question on the PHP thread.

IMN logo majestic logo threadwatch logo seochat tools logo