Well, once again I didn't read the request carefully enough. My apologies and thank you Matt for pointing it out and providing the answer. Your query works perfectly fine: I created the tables using the data Andy provided and tried it... Since I came to like the syntax using JOINS I include it as well for completeness' sake. SELECT s.uid, s.title FROM news_stories s INNER JOIN news_desk_stories ds ON s.uid=ds.story INNER JOIN news_desk d ON d.uid=ds.desk LEFT JOIN news_desk_stories ds2 ON ds2.story=ds.story AND ds2.desk!=ds.desk WHERE d.title = 'private' AND ds2.story IS NULL -----Original Message----- From: thelist at lists.evolt.org Sent: Thursday, March 31, 2005, 1:23:14 AM > Richard, > On Thu, 31 Mar 2005 00:16:49 +0200, Richard Harb <rharb at earthling.net> wrote: >> SELECT ns.uid, ns.title, nd.title >> FROM news_stories ns >> JOIN news_desk_stories nds ON ns.uid=nds.story >> JOIN news_desk nd ON nd.uid=nds.desk >> WHERE nd.title = 'private' > This would still return id=3, which is both private and public. Andy > wanted only results where the desk was *strictly* private (i.e., no > other desk). > I am actually not sure how one could do this with a single query, > without subquery support. It would certainly require a negated self > join. > This is untested and probably will not work. But, I'm still throwing > it out there because I think it's in the right direction: > SELECT ns.uid, ns.title > FROM news_stories s, news_desk_stories ds, news_desk d > LEFT OUTER JOIN news_desk_stories ds2 > ON ds2.story = ds.story AND ds2.desk != ds.desk > WHERE s.uid = ds.story > AND d.uid = ds.desk > AND d.title = 'private' > AND ds2.story IS NULL > Basically, I am joining the three tables normally. Then, additionally, > I am performing an join (outer) again on news_desk_stories, joining > new_desk_stories with itself on the condition that story=story (so, > the same story). The second join condition is that the desks are > different. If these two conditions are satisfied, then this means > there are more than one desks assigned. We don't want this. Since it's > an outer join, when the condition is not satisfied, the left table > results will still be returned, and the right side of the result will > be NULLs. So, we filter on this NULL (which means that there was only > one desk assigned, and it was 'private'). > Give it a shot. Tweak it if you can. This is an intriguing problem, so > if it doesn't work and you'd like further help, I'm willing (if you > sent me the SQL you used to create these tables, it would make it > easier for me to work it out on my local MySQL instance). > Hope this helps. > -- > Matt Warden > Miami University > Oxford, OH, USA > http://mattwarden.com > This email proudly and graciously contributes to entropy.