www.webdeveloper.com
Results 1 to 8 of 8

Thread: [RESOLVED] Triple join to get data from 3 tables

  1. #1
    Join Date
    Dec 2010
    Posts
    232

    resolved [RESOLVED] Triple join to get data from 3 tables

    I have 4 tables in my db, a users table (primary key: user_id), a roles table (primary key: role_id), a teams table (primary key: team_id) and a a users_teams table (no primary key).

    The users_teams is a join table to indicate what user is on each team and what their role is in the team. It uses all the above stated primary keys as foreign keys.

    At the moment I have a query which gets what users are in each team and displays these on the users own page like this:-

    SELECT teams.* FROM teams LEFT JOIN users_teams ON (teams.team_id=users_teams.team_id) WHERE users_teams.user_id = $userid

    I want to also be able to get ALL the info relating to each users role within the team from the roles table in the same query. How can I do this?

    My table structure id:-

    USERS
    ______________________________________________

    user_id | first_name | surname


    TEAMS
    ______________________________________________

    team_id | team_name


    ROLES
    ______________________________________________

    role_id | role_name


    USERS_TEAMS
    ______________________________________________

    user_id | band_id | role_id
    Last edited by jimmyoneshot; 03-13-2011 at 04:54 PM.

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    It amazes me how even the people with 10+ years of experience haven't decided on whether or not a table should be plural ("team" vs "teams").

    If you switch your table engine to innoDB you can use true foreign keys and use inner joins. You could switch your roles around like I explain it here: http://www.webdeveloper.com/forum/sh...d.php?t=242938
    It makes it easier on the application layer (I think anyways), plus I think its fun using bitwise operators.

    Anyways, once you set up your unique constraints etc etc this should work:
    PHP Code:
    $query "
    SELECT * 
    FROM users 
    INNER JOIN user_teams
      ON user.user_ID = user_teams.user_ID
    INNER JOIN teams
      ON teams.team_ID = user_teams.team_ID
    INNER JOIN roles
      ON role_ID = user_teams.role_ID

    WHERE users_teams.user_id = %d"
    $userid); 
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  3. #3
    Join Date
    Dec 2010
    Posts
    232
    Thanks very much Eval.

    I want to be able to advance this just slightly by including images representing each team. You see I have a seperate table called photos set up in the following way:-

    PHOTOS
    ________________________

    photo_id of_id photo_filename is_main

    The of_id is used to indicate which team or user the image is of i.e it corresponds to either the user_id of users or the team_id of teams. The is_main field is used to determine if the image is the main 'profile' image for that user/team. 1 = true, 0 = fasle

    How can I modify the query you've provided so that the images relating to each team the user is in which are set as is_main = 1 are also included?



    I've tried the following to no avail:-

    SELECT teams.*, roles.*, photos.* FROM teams LEFT JOIN users_teams ON (teams.team_id=users_teams.team_id) LEFT JOIN roles ON (roles.role_id = users_teams.role_id) LEFT JOIN photos ON (photos.of_id = users_teams.team_id) WHERE users_teams.user_id = $userid
    Last edited by jimmyoneshot; 03-13-2011 at 10:02 PM.

  4. #4
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Well I would personally do this:

    I would handle photos like so:

    Code:
    CREATE TABLE Photo (
    Photo_ID INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    alt varchar(100) NULL DEFAULT "",
    path varchar(100) NOT NULL
    ) ENGINE=innoDB COLLATION=utf8_bin;
    
    ALTER TABLE Photo ADD UNIQUE ('path');
    
    CREATE TABLE TeamPhoto (
    TeamPhoto_ID INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Team_ID INT(11) UNSIGNED NOT NULL,
    Photo_ID INT(11) UNSIGNED NOT NULL,
    display_order INT(4) UNSIGNED NOT NULL,
    ismain BOOLEAN NULL DEFAULT 0
    ) ENGINE=innoDB COLLATION=utf8_unicode_ci;
    
    ALTER TABLE TeamPhoto ADD UNIQUE ('Photo_ID');
    ALTER TABLE TeamPhoto ADD UNIQUE ('Photo_ID', 'Team_ID');
    
    CREATE TABLE PlayerPhoto (
    PlayerPhoto_ID INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Player_ID INT(11) UNSIGNED NOT NULL,
    Photo_ID INT(11) UNSIGNED NOT NULL,
    display_order INT(4) UNSIGNED NOT NULL,
    ismain BOOLEAN NULL DEFAULT 0
    ) ENGINE=innoDB COLLATION=utf8_unicode_ci;
    
    ALTER TABLE PlayerPhoto ADD UNIQUE ('Photo_ID');
    ALTER TABLE TeamPhoto ADD UNIQUE ('Photo_ID', 'Player_ID');
    I didn't put very much thought into it, but I think it's a good first hack at it. One thing you may consider is getting aquastudio- its free and if you set up true foreign keys it will generate a really really nice ERD for you, then you can look at your ERD and see if it makes sense. Unfortunately that and parsing data (excel and stuff) is about all AquaStudio seems really outstanding at- it is otherwise quite a hassle compared to using SSH and mysql from the terminal.

    Cheers

    Edit: I added better collations, *nix/apache will serve foo.JPG differently than foo.jpg
    Last edited by eval(BadCode); 03-13-2011 at 10:34 PM.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  5. #5
    Join Date
    Dec 2010
    Posts
    232
    You're right Eval and that is a great setup you've shown there which seems more valid. I may modify mine to look like that.

    How do I actually get the photos from the team photos from the teamphoto table within the same query you shown me a couple of posts above though?

    Basically in my page I have a user profile page with a tab on it called teams. When clicked this will list all of the teams that the user is in and each row/item in that list will contain the team name, the user in question's role in the team and the team's main photo/profile image.

    This is why I wanted to work this into my existing query as in the past the way I'd do it is do a query inside every single team item in that list to get the images which is obviously a bit too much work on the server annd ill slow things down.

    I'm trying to somehow work everything into one query but it's sql statements that confuse me though.

  6. #6
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    No-
    Photo <-PlayerPhoto <-[User(aka Player) <-UserTeams-> Teams]-> TeamPhoto-> Photo

    I don't like it (I know I proposed it). I don't really want to scatter photos out either- I think they belong in 1 table... maybe this:

    Code:
    CREATE TABLE Photo (
    Photo_ID INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `Owner_ID` INT(11) UNSIGNED NOT NULL,
    `type` SET('unknown','player','team','main') NULL DEFAULT 1,
    `alt` VARCHAR(100) NULL DEFAULT "",
    `path` VARCHAR(150) NOT NULL,
    display_order INT(4) UNSIGNED NOT NULL
    ) ENGINE=innoDB COLLATION=utf8_bin;
    
    ALTER TABLE Photo ADD UNIQUE ('path');
    
    
    Saun Roberts (player_ID = 1823) main photo insert:
    
    INSERT INTO Photo VALUES 
    (NULL, 1823, 10, 'Saun Roberts Photo', '~img/players/IMG_2851.jpg', 1);
    
    Detroit Hitters (Team_ID = 192) main photo insert:
    (NULL, 192, 12, 'Detroit Hitters Team Photo', '~img/players/IMG_1512.jpg', 1);
    It's odd because your foreign key could refer to two different things. I'm sure there's a better solution out there. I say you could keep the SET() so you have choices about where to start in your FROM clause, then put the "main photo" into the user's / team table. I get the feeling that 'main' photos will be selected more often than the ones which are not 'main'.

    Its up to you. I say forget that roles table tho (its trouble) a SET clicks really well with the idea of 'role' based anything, as long as you have less than 64 roles

    Best of luck.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

  7. #7
    Join Date
    Dec 2010
    Posts
    232
    Sorry for the late reply. Was away for a few days. I've managed to get something which relates to your original solution working. It was the fact that I was using of_id rather than user_id as a foreign key. I now use both user_id and team_id in the photos table that way if the image is applied to a user the team_id will be null/0 and the user_id will contain the relevant user_id whereas if the photo applies to a team the team_id will have a relevant team value and the user_id will be 0/null.

    Thanks for your help eval.

  8. #8
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    I would like to see what other people would do. Ribeyed, yamaharuss, criterion9, NogDog, and svidgen probably would have solved it somewhat differently.

    I'm not entirely satisfied with the schema.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles