CODEWITHSUNDEEP
phpmysqlsql
Marcus
Marcus

Reputation: 295

Mysql select table which depends on other two tables

I have three table group, user and belongs where belongs is relation between group and user.

Table group
group_id (pk)
group_name

Table user
user_id (pk)
user_name

Table belongs
user_id
group_id

I want to show a random groups that people in specified group are also in.
For example specified group is where group_id = 1, Of course, i can get people in this group by use

"SELECT user_id FROM user u JOIN belongs b ON u.user_id = b.user_id and b.group_id = '1'"

I have been trying, and now, i have no idea how to select group that these people are in too.

 "SELECT g.* FROM group g WHERE ..."

Thanks in advance.

Upvotes: 1

Views: 89

Answers (2)

user3175748
user3175748

Reputation:

You can get a list of all the other groups associated with people in group 1 by using a sub-query:

SELECT g.group_name, COUNT(g.group_name)
FROM group g
INNER JOIN belongs b ON b.group_id = g.group_id
WHERE b.user_id IN ( SELECT u.user_id FROM user u JOIN belongs b ON u.user_id = b.user_id and b.group_id = '1' )
GROUP BY g.group_name
ORDER BY COUNT(g.group_name) DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270633

You can get all the groups by using an extra join:

SELECT DISTINCT b2.group_id
FROM user u JOIN
     belongs b
     ON u.user_id = b.user_id and b.group_id = '1' JOIN
     belongs b2
     ON b2.user_id = b.user_id;

One way to choose a random such group is to use order by rand() limit 1. You might want to add where b2.group_id <> '1'.

Assuming that you want to count the members of group_id = 1 in these groups, you would just use group by:

SELECT b2.group_id, count(distinct b2.user_id) as numusers
FROM user u JOIN
     belongs b
     ON u.user_id = b.user_id and b.group_id = '1' JOIN
     belongs b2
     ON b2.user_id = b.user_id
GROUP BY b2.group_id;

The join to user is superfluous, but I have left it in assuming that it might be used for some other conditions not in the original question.

Upvotes: 2

Related Questions