[thelist] Need help from any mySQL gurus on the list

Richard Harb rharb at earthling.net
Wed Mar 30 18:23:56 CST 2005


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.



More information about the thelist mailing list