CODEWITHSUNDEEP
phpmysql
armate1hernan
armate1hernan

Reputation: 105

SELECT users and COUNT shared items in MySQL

I have a table with users

enter image description here

And a table with "shared" events

enter image description here

if I'm Scott (ID = 1), how I can select ONLY users who share my events AND add a special column with number off shared events like this

enter image description here

---- SOLVED ----

These two queries do the job:

Thanks to user6368519

SELECT
    t2.ID,
    t2._name,
    t2._email,
    COUNT(*) _shared
FROM _user_events t1 
INNER JOIN _users t2 ON ( t1._user = t2.id )
WHERE _event IN ( SELECT _event FROM _user_events t3 WHERE t3._user = 1 )
AND t1._user <> 1
GROUP BY 1,2

Thanks to S. Garces

SELECT
    users.ID,
    users._name,
    users._email,
    (
        SELECT COUNT(*)
        FROM _user_events
        WHERE _user = users.ID
        AND _event IN (
            SELECT _event FROM _user_events WHERE _user = 1
        )
    ) as _shared
FROM _users users
WHERE ID != 1
AND ID IN (
    SELECT _user
    FROM _user_events
    WHERE _event IN (
        SELECT _event FROM _user_events WHERE _user = 1
    )
)

Upvotes: 2

Views: 102

Answers (3)

user6368519
user6368519

Reputation: 74

If you do not mind a single MySQL statement:

SELECT t1._user ID,t2._name,t2._email,COUNT(*) _shared
    FROM shared t1 INNER JOIN user t2 ON (t1._user=t2.id)
    WHERE _event IN (SELECT _event FROM shared t3 WHERE t3._user=1)
        AND t1._user<>1
    GROUP BY 1;

Upvotes: 1

SantiG
SantiG

Reputation: 828

try this:

SELECT user.ID, user._name user._email, 
(SELECT count(*) FROM shared_event where _user = user.id and _event in
  (SELECT _event from shared_event where _user = 1)
) as _shared
FROM USER WHERE ID != 1

Upvotes: 1

SMA
SMA

Reputation: 37063

Try using self join on shared and joining with users table like:

SELECT id, name, email, id
FROM users u INNER JOIN
    (SELECT DISTINCT s2._user
    FROM shared s1 INNER JOIN Shared s2
    ON s1._event = s2._event
    WHERE s1._user = 1) temp
ON u.id = temp.user

Upvotes: 0

Related Questions