0

I have this database

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)
);

I want to find if there is a message that has been sent more than one times between users.

for example if between 1 have sent the message "Hello" to 5 and also 4 have sent it to 8 for example, I want to print the message "Hello". But if it is only sent one time then I don't want it. I want at least two times to be repeated but at different couples of users. I don't want it to appear if the users 1 and 5 have sent it 2 times.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
GIORGOSMAZ
  • 23
  • 2
  • Possible duplicate of [Find duplicate records in MySQL](https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) – Masoud Keshavarz May 16 '19 at 07:09

1 Answers1

1

We can try the following query:

SELECT message
FROM Messages
GROUP BY message
HAVING
    MIN(LEAST(sender, recipient)) <> MAX(LEAST(sender, recipient)) OR
    MIN(GREATEST(sender, recipient)) <> MAX(GREATEST(sender, recipient));

This logic flags any message which appears more than once and involves two or more different senders or two or more different recipients.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I don't think that will work for the case where 1 sends to 5 and 5 sends to 1. It's not 100% clear but I don't think those messages are supposed to show either. – Nick May 16 '19 at 07:05
  • @Nick I fixed it. Really hard to get it right the first time, as the OP provided no sample data (nor did I take the time to setup a demo). – Tim Biegeleisen May 16 '19 at 07:07
  • guys i've tried this SELECT COUNT(Messages.message), Messages.message FROM Messages GROUP BY Messages.message HAVING COUNT(Messages.message) > 1 But it doesnt work for the case where use 1 sends to 5 the message Hi twice. i want it to be once but from different users. – GIORGOSMAZ May 16 '19 at 07:18
  • Buon giorno Giorgo, please include your attempted query in the _question_, not here. Comments here should be about my answer. – Tim Biegeleisen May 16 '19 at 07:20