shammon shammon - 3 months ago 10
PHP Question

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

Answer

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.

Comments