homepage Welcome to WebmasterWorld Guest from 54.227.34.0
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
replacement value for when left join condition is not met.
nelsonm

5+ Year Member



 
Msg#: 4363223 posted 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

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



 
Msg#: 4363223 posted 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

5+ Year Member



 
Msg#: 4363223 posted 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



 
Msg#: 4363223 posted 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

5+ Year Member



 
Msg#: 4363223 posted 1:59 am on Sep 19, 2011 (gmt 0)

Thanks arms,

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

thanks.

ticboxsp



 
Msg#: 4363223 posted 3:15 am on Sep 20, 2011 (gmt 0)

excellent just what I was looking for, thanks

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved