replacement value for when left join condition is not met.

Forum Moderators: open

Message Too Old, No Replies

replacement value for when left join condition is not met.

nelsonm

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

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.

rocknbil

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

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'; }

nelsonm

7:27 pm on Sep 16, 2011 (gmt 0)

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

thanks.

arms

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

nelsonm

1:59 am on Sep 19, 2011 (gmt 0)

Thanks arms,

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

thanks.

ticboxsp

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

excellent just what I was looking for, thanks