Web Analytics Made Easy -
StatCounter Selecting reocrds from mulitple tables - CodingForum


No announcement yet.

Selecting reocrds from mulitple tables

  • Filter
  • Time
  • Show
Clear All
new posts

  • Selecting reocrds from mulitple tables

    Ok, you guys were a big help last time. Now i'm doing something similar only i need to join two tables. It's basically the same as the ranking the db question but with a little more complexity. For example.

    One table is called zones, and only consists of two columns ZONEID and ZONE. ZONEID is a shortname for the zone, first three letters and ZONE is full name.
    Like say a zone was Jupiter. ZONEID=JUP ZONE=Jupiter

    Second table consists of all the zones shortnames which match the zoneid in the first table. The data underneath is a list of points for users in that zone. What I want to do is show the top five zones based on how many pts total are underneath that zone.

    SO if I got a table called tblusers
    John 6 |5|
    pete 7 |7|

    The top zone would be Pluto with 13 then Jupiter with 12.
    But i don't want to display the shortname, i want to display the longname in the the ZONES table as it corresponds to the shortname in the tblusers.

    So combining the tables is confusing and matching the shortname with the longname in another table equally as confusing. Am I on the righ track here with this code?

    mySQL = "SELECT TOP 5 SUM(PLU,JUP) FROM tblUsers AS zonepts UNION ALL SELECT FROM zones WHERE zoneid = zonepts ORDER BY SUM(zones.PLU, zones.JUP) DESC"

    I think i'm way off, but i'm learning. I don't even know if you have to use the UNION all anymore. :P Hope the question was clear!

  • #2
    you will need to do a join on the common field these two tables
    have, which is the short name for the zone, not a union.