4

If someone could help me with the query below it would be great! This query is quite challenging, and I tried to do so on my own, but couldn't display the correct result...

I work with DB of a system that is alike "Facebook".

The query should display the email & name of users who wrote a comment for every post, that posted by each of their friends, in the last year.

Meaning, I would like to display ONLY the people who commented on ALL the posts of ALL their friends.


This is my trial, but I received partial results. I cannot understand what I have done wrong.

select distinct U.Mail, U.FirstName + ' ' + U.LastName as FullName 
from Users U
inner join FriendsList FL on U.Mail = FL.Mail1
inner join Post P on FL.Mail2 = P.UserMail
left outer join Comment C on P.ID = C.IDPost and P.UserMail <> C.Mail
where datediff(year, P.DatePosted, getdate()) <= 1
group by U.Mail, U.FirstName, U.LastName
having count(distinct P.ID) = count(distinct C.IDPost)

Link to BAK file (including test data): https://file.io/SagvM3cx

Tables diagram: enter image description here


EXAMPLES:

Let's assume that I & Adam & Ben are friends.

⭐️ First case:

  • Adam wrote 1 post, and I commented.
  • Ben wrote 1 post, and I did not comment.

Then, my name should not display in the query. 👎

  • Adam wrote 1 post, and I commented.
  • Ben wrote 1 post, and I commented.

Then, my name should display in the query.👍

⭐️ Second case:

  • Adam wrote 2 posts, and I commented on the first one but did not comment the second.
  • Ben wrote 1 post, and I commented.

Then, my name should not display in the query.👎

  • Adam wrote 2 posts, and I commented both.
  • Ben wrote 1 post, and I commented.

Then, my name should display in the query.👍

⭐️ Third case:

  • Adam wrote 2 posts, and I commented both.
  • Ben wrote 1 post, and I commented.
  • Mark (Not my friend) wrote 1 post, and I did not comment.

Then, my name should display in the query.👍


EDIT: I added 8 queries - 4 create table and 4 insert. According to this example, the query above should display only Kelly

create table Users
(
Mail nvarchar (20) primary key check(Mail like '_%@_%._%' and (Mail like '%[0-9]%' Or Mail like '%[a-z]%'Or Mail like '%[A-Z]%')),
Password nvarchar (8) check (Password like '%[0-9]%' and Password like '%[az]%' and len(password) <= 8) not null,
FirstName nvarchar (20) not null,
LastName nvarchar (20) not null,
BirthDate date check (datediff(year,BirthDate,getdate())>=18) not null,
JoinDate date check (JoinDate<=getdate()) not null,
Gender nchar(1) check(Gender = 'F' or Gender = 'M' or Gender = 'O'),
NickName nvarchar(20),
Photo nvarchar(20),
Phone bigint check (Phone like '%[0-9]%' and len(Phone) <= 10) not null
)

INSERT INTO Users
VALUES
('[email protected]','k1000000','Kelly','Ka','1992-05-15','2016-09-04','F','Kelly','Kelly.jpg','546296100'),
('[email protected]','l1101111','Lilly','La','1999-04-03','2012-04-04','F','Lilly','Lilly.jpg','542448300'),
('[email protected]','m120121','Moshe','Ma','1995-06-03','2011-04-02','M','Moshe','MosheMa.jpg','542840111'),
('[email protected]','n130131','Nelly','Na','1994-03-07','2020-04-13','F','Nelly','NellyNa.jpg','541234567');
('[email protected]','o140141','Owen','Oa','1992-02-02','2020-05-13','M','Owen','OwenOa.jpg','541234567');

create table FriendsList
(
Mail1 nvarchar (20) references Users(Mail) not null,
Mail2 nvarchar (20) references Users(Mail) not null,
DateAdding date check (DateAdding<=getDate()) not null,
primary key (Mail1,Mail2)
)

INSERT INTO FriendsList
VALUES
('[email protected]','[email protected]','2018-04-18'),
('[email protected]','[email protected]','2020-04-22'),
('[email protected]','[email protected]','2020-04-22'),
('[email protected]','[email protected]','2020-04-22'),
('[email protected]','[email protected]','2018-04-18');
('[email protected]','[email protected]','2020-04-22'),

create table Post
(
ID int identity(1,1) primary key,
Photo nvarchar(20),
Text nvarchar(200),
Location nvarchar(50),
Video int,
DatePosted date check (datediff(month,DatePosted,getdate())<=3) not null,
UserMail nvarchar (20) references Users(Mail) on delete cascade on update
cascade not null
)

INSERT INTO Post
VALUES
('','my name is nellu','','','2020-04-18','[email protected]'),
('','hii','','','2020-05-19','[email protected]');

create table Comment
(
IDPost int references Post(ID) on delete cascade on update cascade not null,
SerialNumComment int check(SerialNumComment > 0) not null,
DateAndTimeComment Datetime check (DateAndTimeComment<=getDate()) not null,
Text nvarchar(200) not null,
Mail nvarchar (20) references Users(Mail) not null,
primary key (IDPost,SerialNumComment)
)

INSERT INTO Comment
VALUES
('1','1','2020-04-18','blabla','[email protected]'),
('2','1','2020-05-05','bhfk','[email protected]');

1 Answer 1

5

The problem in your original query is that

left outer join Comment C on P.ID = C.IDPost and P.UserMail <> C.Mail

Should be

left outer join Comment C on P.ID = C.IDPost and  U.Mail = C.Mail

You are trying to get comments posted by the original user

Personally I find the double NOT EXISTS approach easier to follow (though the semantics of how to treat Users with no eligible posts to comment on differs).

You are looking for Users where there is no recent post from one of their friends that does not have a comment from that User.

This implements that logic

SELECT U.Mail,
       U.FirstName + ' ' + U.LastName AS FullName
FROM   Users U
WHERE  NOT EXISTS (
                  --A post by one of their friends in the last year
                  SELECT *
                   FROM   FriendsList FL
                          INNER JOIN Post P
                                  ON FL.Mail2 = P.UserMail
                   WHERE  U.Mail = FL.Mail1
                          AND P.DatePosted >= DATEADD(YEAR, -1, GETDATE())
                          AND NOT EXISTS (
                                         --A comment by the user on that post
                                         SELECT *
                                          FROM   Comment C
                                          WHERE  P.ID = C.IDPost
                                                 AND U.Mail = C.Mail))
6
  • Thanks! however, according to this attitude - if a person has no friends or his friends did not publish any post - this query displays the person. How would you avoid these edge cases?
    – Shir K
    Commented May 4, 2020 at 17:09
  • 1
    Arguably the query is still correct, if they have 0 posts from friends and comment on 0 of them they still have commented on all the relevant posts. The GROUP BY ... COUNT will treat the empty set differently though. Commented May 4, 2020 at 17:11
  • 1
    The query has never been executed as I don't have your tables or data. Looks OK but may be I left a bug in it. Add CREATE TABLE and INSERT statements to your question. Commented May 5, 2020 at 12:11
  • 1
    AND P.UserMail = C.Mail should be AND U.Mail = C.Mail - the other people additional to kelly are because of the empty set treatment, they have no friends with posts to comment on (are not returned by SELECT DISTINCT Mail1 FROM FriendsList FL INNER JOIN Post P ON FL.Mail2 = P.UserMail even before the date filter) Commented May 5, 2020 at 13:37
  • 1
    but I'll have a look at your GROUP BY anyway as treatment of empty sets with that seems to be more what you want Commented May 5, 2020 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.