homepage Welcome to WebmasterWorld Guest from 54.198.139.141
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:4363225
 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




msg:4363442
 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




msg:4363533
 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:4364074
 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




msg:4364108
 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:4364620
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved