MySQL 5 - To left join or not?

hej, I was wondering if some of you wonderful people could give me a heads up on this,

I have been reading up and have summised that I need a LEFT JOIN

As i want it to list all the teams regardless of if they have points or not.

I have a table of teams with a UID and the team name.

then i have a table called points that stores the UID of the teams table as a foreign key, the number of points and each entry is timestamped for historical review later on.

what I want to do is display all the teams and the total number of points each team has acrued.

I get the sum of the teams with this
$query = "SELECT pofid, SUM(teampoints) FROM points GROUP BY pofid";

I presumed that being a relational database i wouldnt have to save the team name in the points table. Assumed it would be possible to get the team names also.

but how to get a query to display the team as well has foxed me.

I got this far
SELECT pofid, SUM(teampoints) AS totalpoints FROM points GROUP BY pofid LEFT JOIN teams ON points.pofid = teams.teid

was I even close? miles out? what did I miss? This seems like it should be so simple.

Am I barking up the wrong tree? should I be looking to create a totals feild in the teams table and update that with a total from the points table. to me that seems like it shouldnt be needed.

another question pops in to my head also;
How do I echo the aliis totalpoints in PHP?

Thanks in advance for any help with this.

the group by goes at the end. any joins need to go right after the FROM SomeTable part because with the joins, you are still selecting the table(s) to select from. And yes, a left join is correct. an inner join (just join a table, not a left/right) will exclude any rows where there isn't a match in the joining table. a left join will select all rows from the first table, join the second table, but will still include any rows from the first table where there are no corresponding rows in the second table.

so:

Couple of things. When joining you should always qualify your table names on the data you are collecting. Granted sometimes you can get away with not doing it if there is no overlap in the column names between the tables, but it is considered "good form" as well as a means of self documenting.

Also keep in mind that with Left Joins - the order of the tables in the listing matters. So you will do a full table scan on the `points` table - rather then the `teams` table - which may or may not be what you are expecting.



Lastly to access the aliased names from the result set with PHP is just like accessing any other column in the result set. I personally used associative arrays/objects and just called for the result by name.

Hope this helped.