+ Post New Thread
Results 1 to 9 of 9
Web Development Thread, CodeIgniter Join Help in Coding and Web Development; I'm using CodeIgniter on a project and I need to do a strange kind of join. I can't get my ...
  1. #1

    Hightower's Avatar
    Join Date
    Jun 2008
    Location
    Cloud 9
    Posts
    4,920
    Thank Post
    494
    Thanked 690 Times in 444 Posts
    Rep Power
    241

    CodeIgniter Join Help

    I'm using CodeIgniter on a project and I need to do a strange kind of join. I can't get my head around it so can anyone help me with an Active Record join.

    My Tables are like this:

    Code:
    FIXTRURES
    
    |-------------|-------------|--------------|---------------|
    |   id        |    date     |    home_id   |     away_id   |
    |-------------|-------------|--------------|---------------|
    |    1        |    20-10-10 |    1         |      2        |
    |-------------|-------------|--------------|---------------|
    
    
    TEAMS
    
    |-------------|-------------|
    |   id        |   name      |
    |-------------|-------------|
    |    1        |  team one   |
    |-------------|-------------|
    |    2        |  team two   |
    |-------------|-------------|
    I need to be able to join to the teams table twice to pluck the home_team and away_team so that the result might look like:

    Code:
    DATE >> 20-10-10
    HOME_TEAM >> team one
    AWAY_TEAM >> team two
    I would greatly appreciate any code that will do this, but also to explain the code for me so I can understand how to think of this for myself would be even greater appreciated.

    I've looked at some Google links but I really can't get my head around it.

    Many thanks!

  2. #2

    ZeroHour's Avatar
    Join Date
    Dec 2005
    Location
    Edinburgh, Scotland
    Posts
    5,706
    Thank Post
    906
    Thanked 1,321 Times in 803 Posts
    Blog Entries
    1
    Rep Power
    445
    Standard mysql would be:
    SELECT * FROM fixtures INNER JOIN teams ON fixtures.home_id=teams.id INNER JOIN teams ON fixtures.away_id=teams.id

    I think.

  3. #3

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,406
    Thank Post
    639
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    324
    I think you have to alias tables when joining the same one. E.g.

    Code:
    SELECT fixtures.*, h.name AS home_team, a.name AS away_team
    FROM fixtures
    LEFT JOIN teams h ON fixtures.home_id = h.id
    LEFT JOIN teams a ON fixtures.away_id = a.id

  4. #4

    ZeroHour's Avatar
    Join Date
    Dec 2005
    Location
    Edinburgh, Scotland
    Posts
    5,706
    Thank Post
    906
    Thanked 1,321 Times in 803 Posts
    Blog Entries
    1
    Rep Power
    445
    Its been a while since I have had to manually write a join, I have a mysql tool for that which is very nice and simple to use (but not free)

  5. #5

    Hightower's Avatar
    Join Date
    Jun 2008
    Location
    Cloud 9
    Posts
    4,920
    Thank Post
    494
    Thanked 690 Times in 444 Posts
    Rep Power
    241
    Quote Originally Posted by webman View Post
    I think you have to alias tables when joining the same one. E.g.

    Code:
    SELECT fixtures.*, h.name AS home_team, a.name AS away_team
    FROM fixtures
    LEFT JOIN teams h ON fixtures.home_id = h.id
    LEFT JOIN teams a ON fixtures.away_id = a.id
    I've translated that to this in Active Record:

    Code:
    $this->db->select('fixtures.*, h.name AS home_team, a.name AS away_team')
                ->from('fixtures')
                ->join('h','fixtures.home_team = h.id','left')
                ->join('a','fixtures.away_team = a.id','left');
            
            $query = $this->db->get();
            print_r($query->row());
    Which produces this error:

    Code:
     A Database Error Occurred
    
     		Error Number: 1146
    Table 'fcms.h' doesn't exist
    SELECT `fixtures`.*, `h`.`name` AS home_team, `a`.`name` AS away_team FROM (`fixtures`) LEFT JOIN `h` ON `fixtures`.`home_team` = `h`.`id` LEFT JOIN `a` ON `fixtures`.`away_team` = `a`.`id`

  6. #6

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    I think this works
    Code:
    SELECT fixtures.date, home.name AS HomeTeam, away.name AS AwayTeam
    FROM (fixtures INNER JOIN teams AS home ON fixtures.home = home.id) INNER JOIN teams AS away ON fixtures.away = away.id;

  7. Thanks to CESIL from:

    Hightower (9th November 2010)

  8. #7

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,406
    Thank Post
    639
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    324
    That's because Active Record isn't intelligent enough to work out aliases. Run it is a string

  9. #8

    Hightower's Avatar
    Join Date
    Jun 2008
    Location
    Cloud 9
    Posts
    4,920
    Thank Post
    494
    Thanked 690 Times in 444 Posts
    Rep Power
    241
    Quote Originally Posted by CESIL View Post
    I think this works
    Code:
    SELECT fixtures.date, home.name AS HomeTeam, away.name AS AwayTeam
    FROM (fixtures INNER JOIN teams AS home ON fixtures.home = home.id) INNER JOIN teams AS away ON fixtures.away = away.id;
    I translated that to:

    Code:
    $this->db->select('fixtures.date, home.short_name AS hometeam, away.short_name AS awayteam')
                ->from('fixtures')
                ->join('teams AS home','fixtures.home_team = home.id','inner')
                ->join('teams AS away','fixtures.away_team = away.id','inner');
                
            $query = $this->db->get();
            print_r($query->row());
    and it worked a treat. Thanks!

    Any chance you could explain it to me?

  10. #9

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,404
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    The key bit is using 'teams AS home' which creates the alias that webman mentioned.

    It is the same technique as renaming the columns using 'AS' so that you van have a result column as home or away from a column called name.

SHARE:
+ Post New Thread

Similar Threads

  1. Join snow leopard to AD
    By itgeek in forum Mac
    Replies: 3
    Last Post: 17th September 2010, 07:57 AM
  2. how to config openfiler join to AD
    By dh613 in forum Wireless Networks
    Replies: 1
    Last Post: 11th July 2010, 05:40 PM
  3. Script to join new domain
    By THERADIOTUBBY in forum Windows Server 2008 R2
    Replies: 4
    Last Post: 2nd July 2010, 08:32 AM
  4. What union to join??
    By edie209 in forum General Chat
    Replies: 29
    Last Post: 21st March 2006, 10:28 AM
  5. Please join in...
    By Dos_Box in forum General Chat
    Replies: 16
    Last Post: 30th September 2005, 09:06 AM

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
  •