[thelist] MYSQL NOT query in M-N?

Gavin Montague gmontague at gmontague.co.uk
Thu Sep 2 10:54:02 CDT 2004


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



More information about the thelist mailing list