CODEWITHSUNDEEP
phpmysqlcodeigniter-3
shamon shamsudeen
shamon shamsudeen

Reputation: 5848

Mysql query fetch all rows that matches the user groups is only one

I have two tables in mysql database

1.loc8_users (id,username,password,email,phone)

2.loc8_users_groups (id,user_id,group_id).

Here loc8_users_groups store the group id's that assign to each users.

I want to fetch all rows from loc8_users table in which a user have only one group

Upvotes: 1

Views: 75

Answers (1)

1000111
1000111

Reputation: 13519

Please give this a try:

SELECT 
U.*
FROM loc8_users U
INNER JOIN 
(
    SELECT 
    UG.user_id
    FROM loc8_users_groups UG
    GROUP BY UG.user_id
    HAVING COUNT(DISTINCT UG.group_id) = 1
) AS t
ON U.id = t.user_id

Explanation:

SELECT 
 UG.user_id
FROM loc8_users_groups UG
GROUP BY UG.user_id
HAVING COUNT(DISTINCT UG.group_id) = 1;

This query returns only those user_ids who are involved in only one group.

We got those user_ids but now we need the user information of those user_ids. So making an INNER JOIN between this query result and loc8_users table on matching user_id would do.

Upvotes: 1

Related Questions