CODEWITHSUNDEEP
mysqlsql
Xiaoxi Chen
Xiaoxi Chen

Reputation: 163

Find the issues of SQL

I got a questions from a test, it seems an concept question or logic question, so no data at all. Please read:

Assuming there's no syntax error and the column names and table names are correct, what's wrong with the MySQL query below?

-- FOR EVERY USER WE HAVE, this is supposed to return the number of messages they sent

select u.user_id, sum(case when um.message_id is not null then 1 else 0 end) as number_of_messages_sent
from users u left join
     users_messages um
     on um.user_id = u.user_id
where um.status = 'sent'
group by u.user_id;

If anyone have any ideas, please help me.

Upvotes: 1

Views: 626

Answers (4)

rds80
rds80

Reputation: 629

The column in the select query is u.user_i and the column in the group by is u.user_id

Upvotes: 0

OLIVER.KOO
OLIVER.KOO

Reputation: 5993

What Went Wrong:

your query supposes to get EVERY user we have but it is only returning user who has sent messages. This is because your WHERE clause filtered out the users who didn't send any messages. Let me illustrate this with an example.

Example:

I made some sample table with data using this code:

CREATE TABLE userz (
  user_id int,
) ;

INSERT INTO userz (user_id)
VALUES
(1),(2),(3)

CREATE TABLE userz_messages(
  message_id int,
  user_id int,
  status varchar(10)
)

INSERT INTO userz_messages (message_id,user_id,status)
VALUES 
(1,1,'sent'),
(2,2,'sent'),
(3,3,'sent'),
(4,4,'notsend'),
(5,1,'sent'),
(6,2,'sent')

userz_table:

user_id
-----------
1
2
3

userz_message table:

message_id  user_id     status
----------- ----------- ----------
1           1           sent
2           2           sent
3           3           sent
4           4           notsend
5           1           sent
6           2           sent

when we perform your query we get this as the result:

user_id      number_of_messages_sent
----------- -----------------------
1           2
2           2
3           1

Notice user with user_id 4 is not in the result set? This is what's wrong with the query. It is not getting EVERY user.

Hope my example helps you get a better understanding of your problem

Upvotes: 0

user2102266
user2102266

Reputation: 539

You are filtering all results with status="sent". Final result is not "all users and masseges". Final result is "All users who sent one or many massages".

This query is all users and the if exists their massages 

SELECT u.user_id, COUNT(um.message_id)
FROM users AS u, users_messages AS um 
WHERE um.user_id = u.user_id
GROUP BY u.user_id;

This query will return Users who sent at least 1 massage. U will not see users who have not sent massage yet.

SELECT u.user_id, COUNT(um.message_id)
FROM users AS u, users_messages AS um 
WHERE um.user_id = u.user_id AND status="sent"
GROUP BY u.user_id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270091

The where clause is turning the left join into an inner join. So it is only returning users who have sent at least one message. That is not the intent of the question.

A better way to write the query uses count() and fixes the problem by moving condition to the on clause:

select u.user_id, count(um.message_id) as number_of_messages_sent
from users u left join
     users_messages um
     on um.user_id = u.user_id and um.status = 'sent'
group by u.user_id;

Upvotes: 1

Related Questions