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

[Matt Warden]

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.