[thelist] MYSQL NOT query in M-N?

Gavin Montague gmontague at gmontague.co.uk
Fri Sep 3 03:51:30 CDT 2004


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

Thanks Ken,
   however the problem with this is that the query can't be expanded to 
check against say, the name or subject of the paper (sorry, I didn't 
mention this in the original query).   So the pseudo query might be 
"All papers on homelessness where Brown didn't contribute."

The two ways I've thought about it is either to do a difference (but I 
don't think this is possible in any implentation of SQL, is it?) Or, in 
MySQL 4.1 there is an aggregate function that works on strings, 
GROUP_CONCAT so I could do the regex on a concat of authors strings if 
the server was running a supporting version (it's not).

As the database isn't that big what I might just do is cheat and add a 
extra attribute in papers called author_concat and maintain this field 
silently.  It's a bit of a kludge but currently its the only viable 
option that I can think of at the minute.

Thanks,
   Gavin



More information about the thelist mailing list