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

[Richard Harb]

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'

(untested)

-----Original Message-----
From: Andy Warwick
Sent: Wednesday, March 30, 2005, 11:19:58 PM
> Hi list

> I'm having trouble formulating a mySQL query to return the results that 
> I need.

> I've got 3 tables: news_stories, news_desks and news_desk_stories, thus:


> news_stories
> -----------
> uid             title
> 1               Karaoke for Comic Relief
> 2               New Intranet launched
> 3               Fundraising Effort


> news_desk
> ---------
> uid             title
> 1               public
> 2               private


> news_desk_stories
> ---------------
> story           desk
> 1               2
> 2               2
> 3               1
> 3               2


> So this means that:

> Karaoke for Comic Relief = private
> New Intranet launched = private
> Fundraising Effort = public and private

> What I can't get is the SQL statement that will return only those 
> stories that are private, and *only* private; so in this instance would 
> return the first two, but not the third, as it is also public.

> So far I have:

>         SELECT                  news_stories.uid as uid, news_stories.title as title
>          FROM                   news_stories, news_desk_stories
>         WHERE                   news_desk_stories.story = uid
>         AND                     news_desk_stories.desk = 2"

> That gets me *all* the private stories, but includes those that are 
> also public at the same time.

> I need some way to limit the result set so only uniquely private 
> stories are returned.

> Anyone?

> TIA

> -- 
> Andy Warwick
> Creed New Media. <http://www.creed.co.uk>