CODEWITHSUNDEEP
mysqlsqldatabase
JimRoussis
JimRoussis

Reputation: 19

How can I find the amount of users that have sent a message to the same users as a specific user. SQL

This is the database I'm working on

CREATE TABLE Users(

    uid int PRIMARY KEY,

    name text,

    phone text

);
CREATE TABLE Messages(

    recipient int REFERENCES Users(uid),

    sender int REFERENCES Users(uid),

    time timestamp NOT NULL,

    message text NOT NULL,

    PRIMARY KEY (recipient, sender, time)

);

The query I need to answer is "Consider all users who have received a message to the user "SomeUser". How many other users have sent a message to all users who have "SomeUser" send?"

I have tried this but is seems to not work, gives count = 0 ( I think it's because I'm asking my DB to tell me if the recipient ,which is one value, is equal to the vector created by ALL.)

SELECT count(*)
FROM Users AS U , Messages AS M
WHERE U.uid = M.sender AND U.name != 'SomeUser'  AND M.recipient =ALL
   (#id of users that received a message from “SomeUser”
    SELECT DISTINCT M.recipient 
    FROM Users AS U , Messages AS M
    WHERE U.name='SomeUser' AND U.uid=M.sender 
    );

In case of these values in this database

INSERT INTO Users VALUES (0,'Maria','1123456123');

INSERT INTO Users VALUES (1,'John','1123456123');

INSERT INTO Users VALUES (2,'Freddy','1123456123');

INSERT INTO Users VALUES (3,'Joanna','1123456123');

INSERT INTO Users VALUES (4,'Jim','1123456123');

INSERT INTO Users VALUES (5,'Luis','1123456123');

INSERT INTO Messages VALUES (0,1,'2012-12-15 12:57:59.99999','Hello there');

INSERT INTO Messages VALUES (0,2,'2012-12-15 21:59:59.9999','Hello there');

INSERT INTO Messages VALUES (0,3,'2001-12-15 20:57:50.999','Hello theret');

INSERT INTO Messages VALUES (1,4,'2012-12-15 11:59:59.999','Hello there');

INSERT INTO Messages VALUES (2,3,'2012-12-15 21:52:50.9999','Hello there');

INSERT INTO Messages VALUES (2,4,'2012-12-15 11:59:59.99999','Hello there');

INSERT INTO Messages VALUES (3,4,'2012-12-15 20:57:50.999999','Hello there');

INSERT INTO Messages VALUES (4,1,'2012-12-15 23:59:59.99999','Hello there');

INSERT INTO Messages VALUES (4,2,'2012-12-15 20:57:59.9999','Hello there');

INSERT INTO Messages VALUES (2,3,'2012-12-15 21:51:59.999','Hello there');

INSERT INTO Messages VALUES (1,4,'2012-12-15 20:51:59.9999','Hello there');

INSERT INTO Messages VALUES (3,2,'2012-12-15 22:59:50.99999','Hello there');

INSERT INTO Messages VALUES (3,2,'2012-12-15 15:59:59.9999','Hello there');

INSERT INTO Messages VALUES (3,4,'2012-12-15 23:22:59.9999','Hello there');

INSERT INTO Messages VALUES (3,0,'2012-12-15 23:01:59.9999','Hello there');

INSERT INTO Messages VALUES (1,5,'2012-12-15 23:01:59.9999','Hello there');

INSERT INTO Messages VALUES (2,5,'2012-12-15 23:01:59.9999','Hello there');

INSERT INTO Messages VALUES (3,1,'2001-12-15 23:59:59.99999','Hello there');

INSERT INTO Messages VALUES (5,1,'1995-12-15 23:59:59.99999','Hello there');

INSERT INTO Messages VALUES (3,5,'2002-12-15 23:01:59.9999','Hello there');

Considering SomeUser = 'Luis' this query needs to return 1 (because the only one that send to uids 1,2 and 3 is 4) and in case SomeUser = 'Maria' the answer needs to be 4

Thanks in advance for your time.

Upvotes: 0

Views: 99

Answers (1)

forpas
forpas

Reputation: 164089

Grouping, joining and finally counting:

select count(*) counter from (
  select m.sender
  from Messages m inner join Users u
  on u.uid = m.sender
  where 
    u.name <> 'Maria' and
    m.recipient in (
      select distinct m.recipient 
      from Messages m inner join Users u
      on u.uid = m.sender
      where u.name = 'Maria')
  group by m.sender
  having 
    count(distinct m.recipient) = 
    (select count(distinct m.recipient) 
     from Messages m inner join Users u
     on u.uid = m.sender
     where u.name = 'Maria')
) t

See the demo.

Upvotes: 2

Related Questions