mysql/php format/syntax left join ARRRRGGHH

[eciym]

MySQL 5.0.27

 

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 pointsfid, SUM(teampoints) FROM points GROUP BY pointsfid";

 

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 pointsfid, SUM(teampoints) AS totalpoints FROM points GROUP BY pointsfid LEFT JOIN teams ON points.pointsfid = 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 alias totalpoints in PHP?

 

Thanks in advance for any help with this.

[kickstart]

Hi

 

You were close:-

 

SELECT teams.teid, SUM(teampoints) AS totalpoints 
FROM teams 
LEFT JOIN points 
ON teams.teid = points.pointsfid
GROUP BY teams.teid

 

The alias is just treated like any other column name in php.

 

All the best

 

Keith

[eciym]

Thankyou very, very much Keith you have saved me from a monster migrane!