3

I would very appreciate if someone could help me out with this query. I can't seem to get the correct result when using case when count()=0 & where


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

I'm trying to write a query that shows the email address, tagging status & the number of posts posted in the past month. The tagging status should be as follows: If tagging 1-2 posts - display "few" If tagging 3-5 posts - display "medium" If tagging 5+ posts - display "many" If not tagging at all - display "none".

Below is the query I wrote. The problem with the query I wrote is that because of the condition in WHERE, I will never receive 'none'.

How can I change the query in order to display the correct result?

select U.Mail, count(P.ID) as PostCount, 
  case 
    when count(P.ID) = 0 then 'none'
        when count(P.ID) <= 2 then 'few'
        when count(P.ID) <= 5 then 'medium'
    else 'many'
  end PostCountCategory
from Users U 
    left join Tagging T on U.Mail = T.Mail
    left join Post P on T.IDPost = P.ID
where datediff(day,P.DatePosted,getdate()) <= 30 --Because of this condition I would never get 'none'
group by U.Mail, U.Gender

Data for example:

The required input should be: Kelly-'none', Lilly-'none', Nelly-'few', Owen-'none'.

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
('Kelly@gmail.com','k1000000','Kelly','Ka','1992-05-15','2016-09-04','F','Kelly','Kelly.jpg','546296100'),
('Lilly@gmail.com','l1101111','Lilly','La','1999-04-03','2012-04-04','F','Lilly','Lilly.jpg','542448300'),
('Nelly@gmail.com','n130131','Nelly','Na','1994-03-07','2020-04-13','F','Nelly','NellyNa.jpg','541234567');
('Owen@gmail.com','o140141','Owen','Oa','1992-02-02','2020-05-13','M','Owen','OwenOa.jpg','541234567');


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-05-08','Nelly@gmail.com'),
('','hii','','','2020-02-19','Lilly@gmail.com');


create table Tagging
(
Mail nvarchar (20) references Users(Mail) not null,
IDPost int references Post(ID) not null,    
TagMail nvarchar(20) references Users (Mail) not null,
primary key (Mail,IDPost);
)

INSERT INTO Tagging
VALUES
('Nelly@gmail.com','1','Kelly@gmail.com'),
('Nelly@gmail.com','1','Owen@gmail.com');
2
  • Have you tried your script? If gives a lot of errors. – Artashes Khachatryan May 8 '20 at 9:55
  • Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN ON you want as part of an OUTER JOIN ON. After a LEFT JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. (A faq.) – philipxy May 8 '20 at 21:12
6

just move where conditions to the join part:

select U.Mail, count(P.ID) as PostCount, 
  case 
    when count(P.ID) = 0 then 'none'
        when count(P.ID) <= 2 then 'few'
        when count(P.ID) <= 5 then 'medium'
    else 'many'
  end PostCountCategory
from Users U 
    left join Tagging T on U.Mail = T.Mail
    left join Post P on T.IDPost = P.ID AND datediff(day,P.DatePosted,getdate()) <= 30
group by U.Mail, U.Gender

also your original condition doesn't alloow SQL Server to use index seek. It would be better to rewrite it this way:

P.DatePosted >= dateadd(day, -30, cast(getdate() as date))
4


First , combine Post table with Tagging table ,where will do the count, like this:

 SELECT T.Mail,COUNT(P.ID) as myCount
 FROM Post as P
    INNER JOIN Tagging as T 
        ON T.IDPost = P.ID
 WHERE datediff(day,P.DatePosted,getdate()) <= 30
 GROUP BY T.Mail

and finally, rejoin it with the Users table (no longer need Group By, if mail it;s unique)

select U.Mail, p.myCount as PostCount, 
  case 
    when p.myCount = 0 then 'none'
    when p.myCount <= 2 then 'few'
    when p.myCount <= 5 then 'medium'
    when p.myCount > 5 then 'many'
  else 'N/A'
  end PostCountCategory
from Users U 
    --left join Tagging T on U.Mail = T.Mail
    left join 
    (
     SELECT T.Mail,COUNT(P.ID) as myCount
     FROM Post as P
        INNER JOIN Tagging as T 
            ON T.IDPost = P.ID
     WHERE datediff(day,P.DatePosted,getdate()) <= 30
     GROUP BY T.Mail
     )as p 
        ON P.Mail = U.Mail
--where datediff(day,P.DatePosted,getdate()) <= 30 --Because of this condition I would never get 'none'
--group by U.Mail, U.Gender

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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