MYSQL/PHP - More advanced MYSQL query question using 3 tables!

Valleriani
Valleriani used Ask the Experts™
on
Using MYSQL & PHP I was recently showed the "JOIN' use for MYSQL, which worked wonders for two tables when I needed to keep data in the same row using a user_id.

Now I have something bigger for another thing. Let us say this for tables & data:

user_table columns
user_id, total_score
1, 100
2, 400
3, 200
4, 100
5, 100


fleet_id columns
fleet_id, group_name
1, Fleet Awesome
2, Fleet CoolDude


user_fleet_table
user_id, fleet_id (The table that points fleet A to user B)
1, 1
2, 1
3, 2
4, 1
5, 2



===

Using the following example and table, Fleet 1, which is "Fleet Awesome, fleet_id1", would have 3 members in it, and a total of 600 points. Fleet 2 would have 2 members, and a total of 300 points.


I already know how I can do this with several queries and php, aka the way I'd do it:
Grab all fleet IDs.
Grab user_id that match fleet_id, aka membercount and id grab for each fleet.
Using the user_ids for each fleet_id, grab total_score for each user, add it together in a variable.

It works fine, however, since I"m using a neat jQuery thing, I was wondering how I can lessen this into maybe one query (specially for searching methods)? If possible an all in one query that spits out the total_score for each fleet using the above table. Is this possible?

If not, what would be the best way to grab the user_ids/fleet_ids all into one query without the adding total_score part yet, so that I can run a simple php/mysql query to grab everyones score and total it with a php variable?

Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
what result are you looking for for above sample data?
HainKurtSr. System Analyst

Commented:
maybe this works

select u.user_id, sum(u.total_score) total_score, f.group_name
from user_fleet_table uf
inner join user_table u on u.user_id=uf.user_id
inner join fleet_id f on f.fleet_id=uf.fleet_id
group by u.user_id, f.group_name
Sr. System Analyst
Commented:
or this one
select f.fleet_id, f.group_name, sum(u.total_score) total_score, count(1) student_count
  from user_fleet_table uf
       inner join user_table u on u.user_id=uf.user_id
       inner join fleet_id f on f.fleet_id=uf.fleet_id
 group by f.fleet_id, f.group_name

Open in new window

Author

Commented:
With minor tweaks worked awesome, thanks a lot!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial