Re: [PHP-DB] IFNULL

[Chris]

Ron Piggott wrote:

How do I add  IFNULL  to the LEFT JOIN query?  I either want the result
from the database or a 0 instead of the word NULL

Ron

SELECT `store_product_profile`.`reference` , inventory.inventory, 
sold_1.quantity, sold_2.quantity, sold_3.quantity

FROM `store_product_profile`

LEFT JOIN (

SELECT `store_product_inventory_control`.`store_product_profile_reference`
, SUM( `store_product_inventory_control`.`quantity` ) AS inventory
FROM `store_product_inventory_control`
GROUP BY `store_product_inventory_control`.`store_product_profile_reference`
) AS inventory ON `store_product_profile`.`reference` =
`inventory`.`store_product_profile_reference`


You don't add it to the join, it goes on a field you're selecting.

select IFNULL(inventory.inventory, 0) AS blah, 

--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] IFNULL used in a UPDATE

[Gurhan Ozen]

Hi Jane,
No questions are lame.
The statement you wrote down wouldn't work as IFNULL() function doesn't work
the way you intended. See
http://www.mysql.com/doc/C/o/Control_flow_functions.html  for this..

I would accomplish it in two statements. Here it is:

SELECT (@a:=(CASE box1 WHEN '' THEN NOW() ELSE box1 END)) from users;
UPDATE users SET box1=@a, box2=NOW() WHERE email='[EMAIL PROTECTED]';

I hope this helps..

Gurhan


-Original Message-
From: jane [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 13, 2002 12:09 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] IFNULL used in a UPDATE


Hello, I am new to SQL so I hope this is not a lame question.

I want to UPDATE two fields ('box1'  'box2') in table 'users'.
One field I want to always set the current time.
The other field I want to put the current time ONLY if it has the value
NULL.

Here is my attempt:

UPDATE users SET IFNULL(box1 = NOW()), box2 = NOW() WHERE eMail =
'[EMAIL PROTECTED]'

Any help would be greatly appreciated.
Jane.

Using:  MySQL 3.22.27



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php