#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    38
    Rep Power
    2

    Left Join display issue


    This is a strange issue that I can't figure out. I have two MySQL tables, each has one common field. The first table (challenge) has all of the basic information, the second table (challengerev) has user submitted info that goes with the data in the first table. I am pulling data to create an xml file using php.

    My problem is this....

    When I use the following code everything is created just fine except 3 particular fields do not populate in the xml file.

    Code:
    $query = "SELECT * FROM challenge LEFT JOIN challengerev ON challenge.id2 = challengerev.id2";
    However, when I switch the tables in the query (like the following code) all of the fields are displayed in the xml file. I cannot do it this way though since it will only return rows from 'challenge' that match up with rows in 'Challengerev'. I want all rows in 'challenge' along with cooresponding data from 'challengerev'.

    Code:
    $query = "SELECT * FROM challengerev LEFT JOIN challenge ON challenge.id2 = challengerev.id2";

    Any ideas what I may be doing wrong?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by cjackson11
    Any ideas what I may be doing wrong?
    no

    since you are using the dreaded, evil "select star" in both cases, all columns of both tables are returned

    if you are having issues accessing the values of all columns, then this is surely because there are identically named columns in both tables

    solution: do not use the dreaded, evil "select star"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    38
    Rep Power
    2
    Ahh, I do have a couple of fields named the same. I should change those that aren't key.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by cjackson11
    I should change those that aren't key.
    no, don't do that

    instead, use column aliases in your query...

    Code:
    SELECT foo.id AS foo_id
         , bar.id AS bar_id
      FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    38
    Rep Power
    2
    Ok, that worked. Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo