SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Washington, DC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with JOIN

    i'm trying to figure out how to use join
    from what i've read online you can use it to connect tables that share id or something like
    for example
    lets say i have 4 tables

    appletype - typeid, name
    applelist - typeid,infoid
    appleinfo - infoid,originid,color
    appleorigin - originid, location

    this is how they're all connected

    appletype.typeid = applelist.typeid
    applelist.infoid = appleinfo.infoid
    appleinfo.originid = appleorigin.originid

    using this i want to SELECT

    appletype.name
    appleinfo.color
    appleorigin.location

    it's not the most efficient database setup, but i figured that's the easiest example i could show clearly
    this can be done easily with 4 queries
    but i'm hoping with the help of JOIN it can be done with 1 or maybe 2 queries

    thanx for the help

  2. #2
    SitePoint Wizard Dangermouse's Avatar
    Join Date
    Oct 2003
    Posts
    1,024
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a database question and there is a section dedicated to it, could a mod move this there?

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Washington, DC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you're right...apologies
    was in this forum and programming PHP
    my brain didn't make the connection sorry

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2004
    Location
    Ontario, Canada
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LEFT JOIN is a very powerful MySQL command because it can cut down query counts exponentially when loading lots of information (no need to use the awful recursive query anymore).

    Here's my attempt at your query:

    Code:
    SELECT appletype.name, appleinfo.color, appleorigin.location
    FROM `appletype`
    LEFT JOIN `applelist` ON appletype.typeid = applelist.typeid
    LEFT JOIN `appleinfo` ON applelist.infoid = appleinfo.infoid
    LEFT JOIN `appleorigin` ON appleorigin.originid = appleinfo.originid
    ORDER BY appletype.name
    That should do the trick.
    Brock Ferguson
    Lead Developer, Caribou CMS
    A Subscription/Membership CMS and Ecommerce Platform - FREE Trial

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Washington, DC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, i'm gonna test that out...

  6. #6
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Washington, DC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no success

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please show a few rows from each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Washington, DC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i rewrote it like this
    and it worked perfectly

    SELECT appletype.name, appleinfo.color, appleorigin.location
    FROM appletype,applelist,appleinfo,appleorigin
    WHERE (appletype.typeid = applelist.typeid AND applelist.infoid =appleinfo.infoid AND appleorigin.originid =appleinfo.originid )
    ORDER BY appletype.name ASC

    is this any difference technically than using the JOIN
    it pulls the same data in the same format as the JOIN would

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, that's the "table list" syntax, with the join conditions included in the WHERE clause

    table list syntax is equivalent to INNER JOIN

    silverlime suggested LEFT OUTER JOIN

    outer joins would be required whenever you have some rows without matching rows in related tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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