Welcome to WebmasterWorld Guest from 50.16.78.128

Forum Moderators: open

Message Too Old, No Replies

replacement value for when left join condition is not met.

   
6:50 am on Sep 16, 2011 (gmt 0)

5+ Year Member



hi all,

other than testing a result field value with an if statement, does mysql offer the ability to set a replacement value in a left join when the condition is not met?

For instance...

SELECT customer.name
FROM workorder
LEFT JOIN customer
ON workorder.id = customer.id ELSE USE 'Not Defined'


So the idea is if there's no matching customer.id, assign 'Not Defined' to customer.name instead of executing the following...

$customer = mysql_fetch_array($result,MYSQL_NUM);

if($customer['name'] == '') $customer['name'] = 'Not Assigned';


thanks.
4:09 pm on Sep 16, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



I think you might have to do this in programming but if there's no value it will be null . . .

$customer = mysql_fetch_array($result,MYSQL_NUM);
if ($customer['name'] == NULL) { $customer['name']='Not Assigned'; }
7:27 pm on Sep 16, 2011 (gmt 0)

5+ Year Member



yea... i thought as much. I looked in the mysql online manual but could not find anything.

thanks.
10:47 pm on Sep 18, 2011 (gmt 0)



Try:

SELECT IFNULL(customer.name, 'Not Defined')
FROM workorder
LEFT JOIN customer
ON workorder.id = customer.id
1:59 am on Sep 19, 2011 (gmt 0)

5+ Year Member



Thanks arms,

I guess i should of looked deeper into the mysql manual.

thanks.
3:15 am on Sep 20, 2011 (gmt 0)



excellent just what I was looking for, thanks
 

Featured Threads

Hot Threads This Week

Hot Threads This Month