Welcome to WebmasterWorld Guest from 54.196.233.208

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)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 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.
4:09 pm on Sept 16, 2011 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 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'; }
7:27 pm on Sept 16, 2011 (gmt 0)

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 0


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

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

New User

joined:Feb 22, 2011
posts:22
votes: 0


Try:

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

Full Member

5+ Year Member

joined:June 30, 2008
posts: 318
votes: 0


Thanks arms,

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

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

New User

joined:Sept 20, 2011
posts:2
votes: 0


excellent just what I was looking for, thanks