Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 3 of 3

Thread: Multiple Joins

  1. #1
    Junior Member weblebowski's Avatar
    Join Date
    Mar 2012
    Posts
    19
    Member #
    31067
    Liked
    1 times
    Right now I have a query that looks like this:

    Code:
    SELECT *
                FROM picks
                INNER JOIN schedules ON picks.game_id = schedules.game_id
                WHERE week =14 and user_id =1
                ORDER BY picks.game_id;
    I also want to include records from teams table when schedules.away_id or schedules.home_id match up with teams.team_id. I just want to be able to access the abbreviations for the teams

    My tables look like this...

    SCHEDULES
    -game_id
    -time
    -week
    -home_id
    -away_id
    -home_score
    -away_score

    PICKS
    -user_id
    -pick_id
    -game_id
    -selection
    -grade

    PLAYERS
    -user_id
    -user_name
    -pw
    -record

    TEAMS
    -team_id
    -team_name
    -abbr

  2.  

  3. #2
    Member
    Join Date
    Jul 2014
    Posts
    33
    Member #
    39531
    Hello.......please use this query for correct data because your query is incomplete.
    SELECT *
    FROM picks
    INNER JOIN schedules ON picks.game_id = schedules.game_id
    WHERE schedules.week =14 and picks.suser_id =1
    ORDER BY picks.game_id;

  4. #3
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    Actually, that's not correct.

    I haven't tried this myself, but this is the general idea.

    Code:
    SELECT * FROM 
    ((picks INNER JOIN schedules ON picks.game_id = schedules.game_id)
    inner join teams t1 on t1.team_id = schedules.home_id)
    inner join teams t2 on t2.team_id = schedules.away_id
                WHERE schedules.week =14 and picks.user_id =1
                ORDER BY picks.game_id;
    The reason you want to create two aliases for the teams table is because you need to create two separate joins...one for the home id, and one for the away ID.

    You'll also want to fine-tune your query so that you only retrieve the information you absolutely have to have, especially if you're say retrieving it into an array. Keep your memory as free as you can.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:41 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com