mySQL Problem

Hi,

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
FROM deskdrops.users,deskdrops.deskdrops
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.

Thanks,

David
MHITSupportAsked:
Who is Participating?
 
plummetConnect With a Mentor Commented:
Hi David

I think you need to do something like this:

SELECT u.username,d.deskdrop_name
FROM deskdrops d
left join reads r
	on r.ddid = d.ddid
inner join users u
	on r.usrid = d.usrid
where r.ddid is null

Open in new window


But with no table definitions I don't know your field names, etc, so there may be changes that could be made. However, this might help.

Regards
John
0
All Courses

From novice to tech pro — start learning today.