Avatar of Valleriani
Flag for Sweden asked on

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

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!
MySQL ServerDatabasesPHP

Avatar of undefined
Last Comment

8/22/2022 - Mon

what result are you looking for for above sample data?

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

With minor tweaks worked awesome, thanks a lot!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes