CODEWITHSUNDEEP
mysql
Frank Gordon
Frank Gordon

Reputation: 91

Collecting data from 3 tables into 1 query

I have three different tables in my MySQL database.

table users: (id, score, name)
table teams: (id, title)
table team_Members: (team_id, user_id)

What I would like to do is to have 1 query that finds every team ID a given user ID is member of, along with the following information:

  1. total number of members in that team
  2. the name of the team
  3. users rank within the team (based on score)

EDIT:

Desired output should look like this;

TITLE (of the group)      NUM_MEMBERS       RANK
------------------------------------------------
Foo bar team              5                 2
Another great group       34                17
.
.
.

The query should be based on the users ID.

Help is greatly appreciated

Upvotes: 3

Views: 96

Answers (2)

David Mårtensson
David Mårtensson

Reputation: 7600

I think this query should get what you ask for

select t.id, t.title, count(m.user_id) members, (
    select count(1)
    from users u3 
    inner join team_Members m3 on u3.id = m3.user_id 
    and m3.team_id = t.id and u3.score > (
        select score from users where name = 'Johan'
    )
) + 1 score
from teams t
inner join team_Members m on t.id = m.team_id
where t.id in (
    select team_id 
    from team_Members m2
    inner join users u2 on m2.user_id = u2.id
    where u2.name = 'Johan'
)
group by t.id, t.title

Upvotes: 1

GTSouza
GTSouza

Reputation: 365

To collect you just need use JOIN

SELECT 
  u.*, t.*, tm.* 
FROM 
  users u 
JOIN 
  team_Members tm ON  u.id = tm.user_id 
JOIN 
  teams t ON t.id = tm.team_id;

To get total of number of that team use COUNT with some group key

Some like that

SELECT 
  COUNT(t.id), u.*, t.*, tm.* 
FROM 
  users u 
JOIN 
  team_Members tm ON u.id = tm.user_id 
JOIN 
  teams t ON t.id = tm.team_id GROUP BY t.id;

And to rank just:

SELECT 
  COUNT(t.id) as number_of_members, u.*, t.*, tm.* 
FROM 
  users u 
JOIN 
  team_Members tm ON u.id = tm.user_id 
JOIN 
  teams t ON t.id = tm.team_id 
GROUP BY t.id 
ORDER BY u.score;

Upvotes: 0

Related Questions