Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Posts
    24

    Unanswered: joining 3 tables

    I have 3 tables:

    'manager'
    manager_id
    manager_name

    'manager_business'
    manager_id
    business_id

    'business'
    business_id
    business_brand

    If I type in the manager name, I would like to see which business_brands he/she manages.

    I am halfway there with this statement:

    SELECT manager_name, business_id
    FROM manager
    LEFT JOIN manager_business ON manager.manager_id = manager_business.manager_id
    where manager_name like '%john%'

    Any help?

  2. #2
    Join Date
    Dec 2002
    Posts
    24
    I may have answered my question, although there maybe a better way:

    select manager_name, business_brand from manager, business, manager_business where manager.manager_id = manager_business.manager_id and manager_business.business_id = business.business_id and manager_name like '%john smith%'

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    voila, the better way --
    Code:
    SELECT manager.manager_name
         , business.business_brand 
      FROM manager
    INNER
      JOIN manager_business 
        ON manager_business.manager_id = manager.manager_id
    INNER
      JOIN business
        ON business.business_id = manager_business.business_id
     WHERE manager.manager_name LIKE '%john smith%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •