MySQL Joins & IsNull()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
justravis
Forum Commoner
Posts: 53
Joined: Mon Dec 16, 2002 3:18 am
Location: San Diego, CA
Contact:

MySQL Joins & IsNull()

Post 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???
Beans
Forum Commoner
Posts: 49
Joined: Mon Dec 23, 2002 3:06 am
Location: Manila, Philippines
Contact:

Post 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...
// ...
justravis
Forum Commoner
Posts: 53
Joined: Mon Dec 16, 2002 3:18 am
Location: San Diego, CA
Contact:

Post by justravis »

thats pretty much what I ended up doing
Post Reply