I am really stuck with a complex sql query... I will explain below what i need to happen.
Basically we have 3 tables, 1 for users, 1 for deskdrops and another for reads..
The deskdrops table cotains a list of documents and when a user clicks on the document it should insert a new record to the reads table and this table only holds a record of the user id and the deskdrop id.
I need to list the deskdrops that have not been read for a user using both the current user id and the deskdrop id from the reads, users and deskdrops table.
I have tried to create a query below but it doesn't work...
SELECT DISTINCT username,deskdrop_name
WHERE deskdrops.users.managerid = '4'
AND users.id NOT IN (SELECT usrid FROM deskdrops.reads)
AND ddid NOT IN (SELECT ddid FROM deskdrops.reads);
Note there could be 400 users and 50 deskdrops, i need to return any deskdrops (documents) that any user has not read, Using the reads table.