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???
MySQL Joins & IsNull()
Moderator: General Moderators
-
Beans
- Forum Commoner
- Posts: 49
- Joined: Mon Dec 23, 2002 3:06 am
- Location: Manila, Philippines
- Contact:
Well, if you're doing this in PHP, you can create an if-else statement for that:
Code: Select all
$art_auth_result = mysql_query("SELECT * FROM art_auth");
if (mysql_num_rows($art_auth_result)>0)
{
$query = "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)";
}
else
{
$query = "SELECT * FROM author";
}
$result = mysql_query($query);
// ...
// Add code here to manipulate the result set...
// ...