[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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
0
MHITSupport
Asked:
MHITSupport
1 Solution
 
plummetCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now