Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder musher's Avatar
    Join Date
    Jan 2005
    Location
    Minnesota
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Difference between types of Joins

    Totaly confused!
    I have two tables menu & page_content (the relationship is many page_content records to one menu record).

    I set up a query to list all the records, worked fine on my local pc but did not when I loaded it to the server, after doing some reading I changed it to a "RIGHT JOIN" and it worked. Now I've done some reading and googleing and this stuff is still not making sense, My question is what is the difference between???
    1. LEFT JOIN
    2. RIGHT JOIN
    3. INNER JOIN
    4. OUTTER JOIN
    and which one should I use???

    Query that didnt work on server (host)
    PHP Code:
    // $result = mysql_query("SELECT a.ID, a.menu_id, a.submenu_title, a.page_title, a.display_order as page_order, b.menu_title, b.display_order as menu_order FROM page_content as a JOIN menu as b ON (a.menu_id=b.ID) ORDER BY b.display_order,a.display_order",$db); 
    Query that did work on server (host)
    PHP Code:
    $result mysql_query("SELECT a.ID, a.menu_title, a.display_order AS menu_order, b.ID AS pageID, b.menu_id AS itis, b.submenu_title, b.page_title, b.display_order AS page_order, b.access_lvl FROM menu AS a RIGHT JOIN page_content AS b ON a.ID = b.menu_id ORDER BY menu_order, page_order",$db); 
    The two query's are a tad bit different, was trying to follow the MySQL manual when I did the final test, but query 2 did work set up exactly like query 1 except that it had a right join in it.
    Thanks
    Jim M

    "Lord, help me to become the person my dog thinks I am" - Dawn Ewing
    "If you must know. Yes, I do enjoy running after the dog sled when I fall off" - Me

    www.huskyzone.com -- Woodland Siberians

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    a left join and a right join are both outer joins. the word outer is optional. you can write them as:

    LEFT JOIN
    or
    LEFT OUTER JOIN

    the difference between an inner join and an outer join is that an inner join will only return records from both tables that match the field you are joining on.

    an outer join will also return unmatched rows.

    if you have a table of cars and car sales and want to show all cars that have been sold you would use an inner join on the two tables.

    if you wanted to list all cars, irrespective of whether or not they have been sold, you would use an outer join.

    you can use left or right joins, but you should stick with one or the other and not mix them up. If you read from left to right then you should use LEFT joins and then you will remember that the first table you name is "on the left" of the second table.

  • #3
    Regular Coder musher's Avatar
    Join Date
    Jan 2005
    Location
    Minnesota
    Posts
    203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    guelphdad,
    Thanks "BIG TIME" makes total sense now, great example.

    PS if your ever in southern MN swing on over I have a cold beer in the fridge for ya
    Thanks
    Jim M

    "Lord, help me to become the person my dog thinks I am" - Dawn Ewing
    "If you must know. Yes, I do enjoy running after the dog sled when I fall off" - Me

    www.huskyzone.com -- Woodland Siberians


  •  

    Posting Permissions

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