MySQL Joins & IsNull()
Posted: Mon Dec 16, 2002 3:18 am
My goal is to create a query that selects authors that are NOT associated with a certain article. This would include authors that are not associated with any articles.
Here are my tables:
AUTHOR
id
first
mid
last
ARTICLE
id
title
body
ART_AUTH (link table)
artid
authid
Here is my query thus far:
SELECT author.id, author.first, author.mid, author.last, FROM author, art_auth WHERE author.id<>art_auth.authid OR (author.id=art_auth.authid AND art_auth.artid<>$artid)
It works GREAT until the link table is empty. Then it returns 0 rows. In that case, I would like it to just return all the authors in the authors table.
I have also tried:
SELECT author.id, author.first, author.mid, author.last FROM author, LEFT JOIN art_auth ON author.id=art_auth.authid WHERE ISNULL(art_auth.authid) OR art_auth.artid !=$artid GROUP BY author.id ORDER BY author.last
and
SELECT author.id, author.first, author.mid, author.last FROM author LEFT JOIN art_auth ON author.id=art_auth.authid WHERE art_auth.artid != $artid GROUP BY author.id ORDER BY author.last
None of them work when the table is empty. Have any sugestions???
Here are my tables:
AUTHOR
id
first
mid
last
ARTICLE
id
title
body
ART_AUTH (link table)
artid
authid
Here is my query thus far:
SELECT author.id, author.first, author.mid, author.last, FROM author, art_auth WHERE author.id<>art_auth.authid OR (author.id=art_auth.authid AND art_auth.artid<>$artid)
It works GREAT until the link table is empty. Then it returns 0 rows. In that case, I would like it to just return all the authors in the authors table.
I have also tried:
SELECT author.id, author.first, author.mid, author.last FROM author, LEFT JOIN art_auth ON author.id=art_auth.authid WHERE ISNULL(art_auth.authid) OR art_auth.artid !=$artid GROUP BY author.id ORDER BY author.last
and
SELECT author.id, author.first, author.mid, author.last FROM author LEFT JOIN art_auth ON author.id=art_auth.authid WHERE art_auth.artid != $artid GROUP BY author.id ORDER BY author.last
None of them work when the table is empty. Have any sugestions???