CODEWITHSUNDEEP
mysqlsqldatabaseselectinner-join
iOSGeek
iOSGeek

Reputation: 5587

Advanced Inner Join between User, User_Group and Subscriptions Tables

I have Three tables:

Users          { id | name | email }
User_Group     { User_id | group_id }
Subscriptions  {user_id, sub_level }

Let's suppose we have:

Table Users:

[1, John, [email protected]]

[2, Lara, [email protected]]

Table User Group:

[1,6]  // User 1 is assigned to Group 6

[1,3]  // User 1 also assigned to Group 3

[2,3]  // User 2 in ONLY assigned to Group 3

Table Subscriptions :

[1, 8] // User 1 have subscription level 8

[2, 8] // User 2 have subscription level 8

[2, 9] // Also User 2 have subscription level 9

What I want to get is:

All UNIQUE users who are assigned to ONLY group 3 and who have at least one subscription.

What I have tried: 

SELECT U.Username, U.email, G.group_id, S.sub_level FROM `Users` AS U
INNER JOIN `User_Group` AS G
ON U.id = G.user_id
INNER JOIN `Subscriptions` AS S
ON U.id = S.user_id
WHERE G.group_id = 3
Limit 0,10

Problem it will display users who are also assigned to others groups like user 1 who is in group 6 and 3. Also it will display duplicate lines because one user can have many subscription levels.

Upvotes: 0

Views: 852

Answers (3)

Larry Lustig
Larry Lustig

Reputation: 51000

Try:

SELECT U.Username, U.email FROM `Users` AS U
INNER JOIN `User_Group` AS G
ON U.id = G.user_id
INNER JOIN `Subscriptions` AS S
ON U.id = S.user_id
WHERE G.group_id = 3
GROUP BY U.UserName, U.EMail

This will join users to all subscriptions and then only to user group 3; then aggregate the results so each user is present only once.

Your original sample showed Group_ID in the output, but it will always be 3. It also showed Sub_Level in the output but that's meaningless since you want only one row per user regardless of Subcription entries -- what value would you want to see?

If you really want those two columns in the result set you can do:

SELECT U.Username, U.email, G.Group_ID, COUNT(S.Sub_Level) FROM `Users` AS U
INNER JOIN `User_Group` AS G
ON U.id = G.user_id
INNER JOIN `Subscriptions` AS S
ON U.id = S.user_id
WHERE G.group_id = 3
GROUP BY U.UserName, U.EMail, G.Group_ID

which will give 3 for group level and tell you how many subscription records were "compressed" into each result row. In place of COUNT you can also use MIN or MAX or (since this is MySQL) the GROUP_CONCAT function which will give you a string list of subscription levels separated by commas.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270593

One method is to use aggregation:

SELECT U.Username, U.email, MAX(G.group_id)
FROM `Users` U INNER JOIN
     `User_Group` G
     ON U.id = G.user_id INNER JOIN
     `Subscriptions` S
     ON U.id = S.user_id
GROUP BY  U.Username, U.email
HAVING MAX(G.group_id) = 3 AND MIN(G.group_id) = 3 ; -- condition on groups

The condition on subscriptions is handled just by the join condition.

This is probably more efficient as:

select u.*
from users u
where exists (select 1
              from user_groups ug
              where ug.user_id = u.id and ug.group_id = 3
             ) and
      not exists (select 1
                  from user_groups ug
                  where ug.user_id = u.id and ug.group_id <> 3
                 ) and
      exists (select 1
              from subscriptions s
              where s.user_id = u.id
             );

For this query, you want indexes on user_groups(user_id, group_id) and subscriptions(user_id). Actually, these indexes are a good idea for both ways of formulating the query.

Upvotes: 2

Tom H
Tom H

Reputation: 47392

SELECT
    U.username, U.email,
    G.group_id,
    S.sub_level
FROM
    Users U
INNER JOIN User_Group G ON
    G.user_id = u.id AND
    G.group_id = 3
INNER JOIN Subscriptions S ON S.user_id = U.id
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM User_Group G2
        WHERE G2.user_id = U.user_id AND G2.group_id <> 3
    )

Upvotes: 1

Related Questions