Page 1 of 1

MySQL Joins & IsNull()

Posted: Mon Dec 16, 2002 3:18 am
by justravis
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???

Posted: Mon Dec 23, 2002 4:08 am
by Beans
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)
&#123;
  $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)";
&#125;
else
&#123;
  $query = "SELECT * FROM author";
&#125;
$result = mysql_query($query);
// ...
// Add code here to manipulate the result set...
// ...

Posted: Mon Dec 23, 2002 2:05 pm
by justravis
thats pretty much what I ended up doing