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_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!
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!
HainKurt
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
With minor tweaks worked awesome, thanks a lot!