user2661416 user2661416 - 6 months ago 15
SQL Question

Friends list and unread messages SQL query giving unexpected result

I have all day trying to get a result of a SQL query but does not give me the expected result.

My tables at which I consult are:

tcc_friends

id(PK AUTO_INCREMENT)
user_from (FK tcc_user (nickname) )
user_to (FK tcc_user (nickname) )


tcc_messages

id (PK AUTO_INCREMENT)
message
reg_time
wasRead
id_room (FK tcc_friends(id))


test records that have currently inserted are:

tcc_friends

id_room user_from user_to
5 hu example@gmail.com
6 hu example222222@hotmail.com


tcc_messages

id message id_room
1 a 5
2 b 5
3 c 3


SQL:

select
u.*,
f.id_room,COUNT(m.id) as newMessages
from
tcc_friends f,
tcc_user u,
tcc_messages m
where
u.nickname = 'hu' IN (u.nickname = f.user_from and f.user_to='hu') or
(u.nickname = f.user_to and f.user_from='hu') AND
(m.id_room = f.id_room and m.wasRead = 0)
GROUP BY
u.nickname;


RESULT:

id nickname id_room newMessages
81 example@gmail.com 5 2


I'm trying to get a user's friends and also add unread messages but displays only friends who have a message and I'd like to show all friends whether or not unread

Can anybody help me? Regards and Thank you all

Answer

First, don't use SELECT *. It's a horrible practice and you should get out of the habit of doing it as quickly as possible.

Second, learn how to use explicit JOINs. Don't list all of your tables in the WHERE clause. That's syntax that has been obsolete (for good reason) for 20 years. That's also what's causing your problem here because all of your joins are INNER JOINs by default.

Try something like this instead:

SELECT
    U.id,
    U.nickname,
    F.id_room,
    COUNT(M.id) as newMessages
FROM
    tcc_Friends F
INNER JOIN tcc_User U ON U.nickname = F.user_from
LEFT OUTER JOIN tcc_Messages M ON
    M.id_room = F.id_room AND
    M.wasRead = 0  -- Are you using camelcase or underscores in column names?? Make up your mind and stick to it.
WHERE
    F.user_to = 'hu'
GROUP BY
    U.nickname;

That isn't going to get you all the way there, because I'm unclear on what exactly you're trying to get as far as number of unread messages - is it unread messages for the user at all? How does example@gmail.com have 2?

Comments