MYSQL, check empty table in where clause?

[fuzzy1]

Hey All,
the following where clause (expurgated) suits my needs right up until the table emailman is empty (which it frequently is) and then it returns an empty set (which is bad;-) This is actually an "AND emailman.statment, but didn't I think the rest was really relevant.

WHERE emailman.related_id != contacts.id 

How can I modify this to allow for emailman to be empty? Something like...

AND (emailman.related_id != contacts.id || NOT EXISTS (emailman.id) )

[NogDog]

If sub-queries are allowed (i.e.: MySQL >= 4.1), perhaps:

AND (emailman.related_id != contacts.id || (SELECT COUNT(*) FROM emailman) = 0) )

Doesn't seem optimal to me in terms of efficiency, but it's all I can think of without a better understanding of the table structures and the functional requirements.

[fuzzy1]

Thanks Nog Dog,
Much to my newbie delight, you know I actually thought of and tried that! Sadly it did not work and I just figured COUNT() wasn't meant to be used in that way. After reading your post I thought I must have just gotten the syntax wrong so tried again with your code, but alas... no joy. I hate posting the great American novel because -- well ... who has time to read it? But in deparation I shall endevor to clarify the intended use. I'll do my best to summarize.

I'm automating the monthly population of a target list to include all contacts from active accounts with valid email addresses BEGINNING two days before the end of the current marketing period (visit_by). One day later the user is reminded by alert that it is time to "send" Campaign Emails to the targets in this list, whereupon the user is re-directed to our campaigns module to select the appropriate target list and click "Send". Clicking send populates our email queue (TABLE 'emailman' ) with the appropriate email data for delivery early the following morning, and trunkates the target list. Following delivery, emailman itself is also truncated. The problem I'm having is KEEPING the list truncated until the marketing period rolls over once the list has been "Sent" to emailman -- hence the "AND emailman.related_id != contacts.id" clause which causes failure if emailman is empty. As a stop gap I have placed a bogus record in emailman which then allows things to run along as desired, but should this record ever be deleted... well -- that would be bad.

$canvas_list_contacts =" select contacts.id, accounts.marketing_group 
FROM contacts,accounts,emailman 
WHERE contacts.agency_name=accounts.name 
AND accounts.visit_by !='0000-00-00' 
AND now() >= date_sub(accounts.visit_by, interval 2 DAY) 
AND accounts.active='on' 
AND contacts.deleted !='1' 
AND contacts.invalid_email !='1' 
AND emailman.related_id != contacts.id"; // if emailman is empty this "AND in the where clause causes the script to fail
$result = @mysql_query ($canvas_list_contacts);//or die ("Query failed - canvas_list_contacts"); // Run the query. 
$numresults=mysql_query($canvas_list_contacts);
$numrows=mysql_num_rows($numresults);

if ($numrows == 0)
	{return;}
else {
//	convert results $canvas_list_contacts to array 
	    while ($row = mysql_fetch_assoc($result)) { 
	    $_group = $row[marketing_group];
	    $key = uniqid(rand(), true); 
	    $targets[] = "('$key','$canvasID','$row[id]','Contacts','null','null'"; 
		} 
	}
foreach ($targets as $key => $value) { 
	$populate_canvas_list = "INSERT INTO prospect_lists_prospects (id,prospect_list_id,related_id,related_type,date_modified,deleted) 
		VALUES $value);"; 
			$result=mysql_query($populate_canvas_list);
}

[fuzzy1]

I Tried the following but get:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in...

$result = " SELECT COUNT(*) FROM emailman ";
$count_inQueue = $row[0]; 
	if ($count_inQueue == 0){
		$canvas_list_contacts =" 
			SELECT contacts.id, accounts.marketing_group 
			FROM contacts,accounts
			WHERE contacts.agency_name=accounts.name 
			AND accounts.visit_by !='0000-00-00' 
			AND now() >= date_sub(accounts.visit_by, interval 2 DAY) 
			AND accounts.active='on' 
			AND contacts.deleted !='1' 
			AND contacts.invalid_email !='1' 
			AND emailman.related_id != contacts.id ";
	} else {
		$canvas_list_contacts =" 
			SELECT contacts.id, accounts.marketing_group 
			FROM contacts,accounts,emailman 
			WHERE contacts.agency_name=accounts.name 
			AND accounts.visit_by !='0000-00-00' 
			AND now() >= date_sub(accounts.visit_by, interval 2 DAY) 
			AND accounts.active='on' 
			AND contacts.deleted !='1' 
			AND contacts.invalid_email !='1' ";
		}

//	If we have no results, die
if ($numrows == 0)
	{return;}
else {
//	convert results $canvas_list_contacts to array 
	    while ($row = mysql_fetch_assoc($result)) { 
	    $_group = $row[marketing_group];
	    $key = uniqid(rand(), true); 
	    $targets[] = "('$key','$canvasID','$row[id]','Contacts','null','null'"; 
		} 
	}