Thread: Multiple JOINS

    #1
  1. from the lab...
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Nov 2004
    Location
    the land of wind and ghosts
    Posts
    1,844
    Rep Power
    560

    Multiple JOINS


    Howdy folks, been a while!

    I'm having a tough time wrapping my mind around getting this query to work. In short, I'm trying to generate a list of email addresses from our user list, based on a few different parameters.

    Basically, I want to get users.username (email address) IF the user is REGISTERED for a particular TENDER OR the user is subscribed to a DIVISION (essentially a subcategorization subscription list). When staff members create a tender, they assign any number of divisions to it, so whoever is subscribed to that particular division will receive an email for it.

    Tables:
    users (PK = user_id)
    tenders (PK = tender_id)
    registrations (PK = reg_id, FK1 = user, FK2 = tender)
    divisions (PK = division_id)
    tender_divisions (FK1 = tender, FK2 = division)
    user_divisions (FK1 = user, FK2 = division)

    What I've been using:
    [MYSQL]
    SELECT
    tender_divisions.division
    ,
    registrations.reg_id
    ,
    users.user_id, users.username
    ,
    user_divisions.division
    FROM
    tenders
    LEFT JOIN
    tender_divisions
    ON
    tender_divisions.tender = 12
    LEFT JOIN
    registrations
    ON
    registrations.tender = 12
    LEFT JOIN
    users
    ON
    users.user_id = registrations.user
    LEFT JOIN
    user_divisions
    ON
    user_divisions.division = tender_divisions.division
    WHERE
    tenders.tender_id = 12
    GROUP BY users.user_id
    [/MYSQL]

    By playing around with it, I can successfully return EITHER the registered users, OR the divisions subscriptions, but not both.

    Thank you in advance!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Code:
    SELECT users.user_id
         , users.username
      FROM users
    INNER
      JOIN registrations
        ON registrations.user = users.user_id
       AND registrations.tender = 12
    UNION 
    SELECT users.user_id
         , users.username
      FROM users
    INNER
      JOIN user_divisions
        ON user_divisions.user = users.user_id
    INNER
      JOIN tender_divisions
        ON tender_divisions.division = user_divisions.division    
       AND tender_divisions.tender = 12
    GROUP 
        BY users.user_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. from the lab...
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Nov 2004
    Location
    the land of wind and ghosts
    Posts
    1,844
    Rep Power
    560
    Outstanding, thank you! It's not letting me add to your rep for whatever reason, but this is perfect!

IMN logo majestic logo threadwatch logo seochat tools logo