Solved

mySQL Problem

Posted on 2012-04-13
1
286 Views
Last Modified: 2012-08-14
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
Comment
Question by:MHITSupport
1 Comment
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
ID: 37841814
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Hi, in this article I'm going to teach you how to run your own site, and how to let people in (without IP). I'll talk about and explain each step... :) By the way, everything in this Tutorial is completely free and legal. This article is for …
Batch, VBS, and scripts in general are incredibly useful for repetitive tasks.  Some tasks can take a while to complete and it can be annoying to check back only to discover that your script finished 5 minutes ago.  Some scripts may complete nearly …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now