MySQL select unreaded messages

So i'm trying to select unread messages with a query, but the results are blank. I need to get the info from a seperate table read so it will know it was read or not. Im trying to do something like a IF statement so if readed not exists it will be unread, but i can't get it fixed

this is my query:

 SELECT * FROM notify 
 INNER JOIN readed ON readed.acc_ID = '26' AND readed.user_ID = '6' 
        AND readed.msg_ID = notify.ID AND readed.readed != '1' OR readed.ID IS NULL 
 WHERE notify.groep = '1'


DB - readed

ID         - int
user_ID    - int
acc_ID     - int
msg_ID     - int
readed     - enum ('0','1')

DB - notify

ID         - int
notfi      - text
thumb_src  - text
title      - text
url        - text
groep      - int

I hope someone know whats the problem!

Solution

The query seems correct to me, except the part with the OR, so I suppose that the problem is with the data. I will first try to show how you could improve the query nevertheless, and then try to show how to debug your data.

First, let's leave away the OR condition since this won't work as expected in a JOIN ON clause.

Second, when comparing integer fields to values, you should not put quotes around the values. This will only worry every person who tries to understand the query because the quotes denote string values, and it will worry (i.e. slow down) MySQL's parser, because it must convert the string values to numbers.

Third, mixing up normal WHERE conditions and JOIN ON conditions is worrying and bad style (IMHO). I always recommend to put only the conditions which actually link the tables into the JOIN ON clause, and other conditions elsewhere.

Following this advice would lead to something like that:

SELECT * FROM
  notify INNER JOIN readed ON
    readed.msg_ID = notify.ID
WHERE
  readed.acc_ID = 26 AND
  readed.user_ID = 6 AND
  readed.readed != '1' AND
  notify.groep = 1

This should do the same as your original query minus the OR part.

Now, since we suspect that there is a problem with the data, we can begin to debug the data. First, leave away the WHERE clause:

SELECT * FROM
  notify INNER JOIN readed ON
    readed.msg_ID = notify.ID

If this returns data, then you at least know that there are rows in the readed table matching rows in the notify table. If it does not return any data, then there are no rows which fit together, and have found the root of your problem.

Provided that the above returns data, re-add the WHERE clause line by line and test after each step. For example, start with

SELECT * FROM
  notify INNER JOIN readed ON
    readed.msg_ID = notify.ID
WHERE
  readed.acc_ID = 26 AND

and continue with

SELECT * FROM
  notify INNER JOIN readed ON
    readed.msg_ID = notify.ID
WHERE
  readed.acc_ID = 26 AND
  readed.user_ID = 6 AND

and so on, testing the query each time.

That way, you hopefully will find out where the problem is. I am convinced that the problem is with the data, not with the query.