CODEWITHSUNDEEP
mysqlsqlselect
user2661416
user2661416

Reputation: 29

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        [email protected]
6             hu        [email protected]

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   [email protected]       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

Upvotes: 0

Views: 82

Answers (2)

paarandika
paarandika

Reputation: 1439

Remove the and m.wasRead = 0 from m.id_room = f.id_room and m.wasRead = 0 in the WHERE clause. By this query you get friends only with messages that have wasRead property 0. By doing above change you will get all the friends and 0 as the count if they don't have any new messages.

Upvotes: 0

Tom H
Tom H

Reputation: 47464

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 protected] have 2?

Upvotes: 1

Related Questions