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

    Join Date
    Jan 2017
    Posts
    20
    Rep Power
    0

    LEFT JOIN tables


    I'm trying to join 2 tables and make post if two of the variables are the same.

    Code:
    $r = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT table1.id, table1.url, table1.name, table1.size, table1.added, table1.views, table1.hits, table1.category, table2.url FROM table1 LEFT JOIN table2 ON table1.url = table2.url WHERE table1.url = table2.url ORDER BY added DESC") or sqlerr(__FILE__, __LINE__);
    just trying to post related things when the 2 url fields from both tables are the same
    the query above doesn't give any results
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    15,976
    Rep Power
    9615
    Code:
    WHERE table1.url = table2.url
    Get rid of that.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    20
    Rep Power
    0
    don't need the where statement? hmm
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    20
    Rep Power
    0
    removed it, still doesn't display the results. hmm
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    20
    Rep Power
    0
    ohh wait.. it displays now but also have results that don't have anything in their url field. that's why i've placed that where statement...hmm
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,663
    Rep Power
    4288
    Originally Posted by pwpx2
    ohh wait.. it displays now but also have results that don't have anything in their url field. that's why i've placed that where statement...hmm
    are you sure you wouldn't prefer an INNER JOIN then?

    because rows that "don't have anything in their url field" are exactly what the LEFT JOIN is supposed to give you

    Comments on this post

    • pwpx2 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    15,976
    Rep Power
    9615
    You have two "url"s in that query, and the second one (really, anything from table2) will be NULL. That's the one you'll see in your code.

    But... this doesn't make sense. Why are you bothering with the second table when there isn't anything in it in the first place?

    Comments on this post

    • pwpx2 agrees
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    20
    Rep Power
    0
    both tables have info in it
    all fields from both tables have info in it
    i'm just trying to display results that have the url field equal because it will show in a list of related things from the second table

    table2 --> i insert some details about a show.. this has name description and url/picture from lets say imdb or tvrage/tvdb and so on
    table1 ---> this is the table with content/comments/other stuff about other shows also

    on the show's info page would display all content/comments related to it; the thing that get them connected is the url field in both tables which i suppose need to match so that it can display related content to their respective show page

    don't know to explain it better
    the INNER JOIN shows no results so far; LEFT JOIN showed all results not just the ones with the same url as the show itself
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    20
    Rep Power
    0
    The INNER JOIN keyword selects records that have matching values in both tables.
    yes. i did some search about inner join and should do what i want but it doesnt display anything when changing to it.. hmm
    yes i have matching values in both tables for url field
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    20
    Rep Power
    0
    fixed it.. yeah inner join works. in the url field i had at the end "/" in one field and in the other field i didnt.
    wow.. that was easy as pie but hard for me since its the first time doing this
    thank you for all the help

IMN logo majestic logo threadwatch logo seochat tools logo