troubleshooting Question

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

Avatar of Valleriani
VallerianiFlag for Sweden asked on
DatabasesPHPMySQL Server
4 Comments1 Solution324 ViewsLast Modified:
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_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!
Sr. System Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros