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_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!
ASKER CERTIFIED SOLUTION
HainKurt
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