?
Solved

mySQL Problem

Posted on 2012-04-13
1
Medium Priority
?
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 10

Accepted Solution

by:
plummet earned 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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