[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