[thelist] MYSQL NOT query in M-N?

Ken Schaefer ken.schaefer at gmail.com
Fri Sep 3 06:30:29 CDT 2004


SELECT
   a.*
FROM
   Papers AS a
INNER JOIN
   Authors_Papers_Join AS b
ON
   a.pid = b.pid
LEFT JOIN
   Authors AS c
ON
   b.aid = c.aid
AND
   c.name <> 'Brown'
WHERE
   a.Description = 'Homelessness'

Does that help?

Cheers
Ken



On Fri, 3 Sep 2004 09:51:30 +0100, Gavin Montague
<gmontague at gmontague.co.uk> wrote:
> >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
> 
> 
> 
> --
> 
> News! - Evolt.org conference for web professionals.
> 17-19 September 2004 in Toronto, Canada.
> Details at http://TOevolt.org
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


More information about the thelist mailing list