[thelist] MYSQL NOT query in M-N?

[Ken Schaefer]

Does this work?

SELECT
   a.pid
FROM
   Authors_Papers_Join AS a
LEFT JOIN
   Authors AS b
ON
   a.aid = b.aid
AND
   b.name <> 'Brown'

?

Cheers
Ken

On Thu, 2 Sep 2004 16:54:02 +0100, Gavin Montague
<gmontague at gmontague.co.uk> wrote:
> I'm having some trouble getting my noggin around the following and
> would be very grateful for any thoughts...
> 
> relevant DB TABLE - (field[s])
> 
> PAPERS - (pid)
> AUTHORS - (aid, name)
> AUTHORS_PAPERS_JOIN - (pid, aid)
> 
> A PAPER can have 0-N authors.
> 
> Say we have paper 1 with authors Smith and Brown, paper 2 with author
> Smith and paper 3 with author Brown.
> 
> Is it possible to build a single query that will return all papers
> where Brown is NOT an author? IE only paper 2?  My stumbling block is
> the paper with two authors.
> 
> Currently the bit of my query that's relevant looks like  SELECT
> distinct PAPERS.*  ...join the three........ AND a.name NOT RLIKE
> "[[:<:]]Brown[[:>:]]"
> 
> So the join gives me
> 
> 1       -       Smith
> 1       -       Brown
> 2       -       Smith
> 3       -       Brown
> 
> and then the second and fourth rows gets chopped but PAPER 1 is still
> in te returns because of Smith!
> 
> Is there a solution to this that can be implemented in MySQL?
> 
> Thanks for any help.
> 
> Gav