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:
user_from (FK tcc_user (nickname) )
user_to (FK tcc_user (nickname) )
id (PK AUTO_INCREMENT)
id_room (FK tcc_friends(id))
id_room user_from user_to
5 hu firstname.lastname@example.org
6 hu email@example.com
id message id_room
1 a 5
2 b 5
3 c 3
f.id_room,COUNT(m.id) as newMessages
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)
id nickname id_room newMessages
81 firstname.lastname@example.org 5 2
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 email@example.com have 2?