MySQL SELECT IFNULL then compare it numerically with other column

Question

I've tried numerous methods to translate NULL to the number 0 upon selection:

SELECT
  ss.id AS staff_id,
  u.realname AS realname,
  ss.amount AS salary_amount,
  IF(s.paid_amount IS NOT NULL, s.paid_amount,0.00) AS paid_amount,
  (ss.amount-s.paid_amount)
FROM f_salary_staff ss
  JOIN user u ON (u.id=ss.user_id)
  LEFT JOIN (
    SELECT staff_id,
      month_year,
      SUM(amount) AS paid_amount
    FROM f_salary
    WHERE month_year='2020-02-29'
    GROUP BY staff_id,month_year
  ) s ON (ss.id=s.staff_id)

I've used IFNULL but that doesn't turn the 0 into any numeric at all hence the selection didn't even list those with NULL values. Is there a way at all to turn NULL values into numerical upon selection and compare it against other columns numerically?

BTW .. down here is the create table statements for both tables.

f_salary_staff

CREATE TABLE `f_salary_staff` (  `id` int(11) NOT NULL AUTO_INCREMENT, `store_id` int(11) NOT NULL,  `user_id` int(11) NOT NULL,  `date_enter` date DEFAULT NULL,  `amount` decimal(12,2) DEFAULT NULL,  `updated` datetime DEFAULT NULL,  `updated_by` int(11) DEFAULT NULL,  `created` datetime DEFAULT NULL,  `created_by` int(11) DEFAULT NULL,  `last_activated` datetime DEFAULT NULL,  `last_inactivated` datetime DEFAULT NULL,  `status` varchar(16) DEFAULT NULL,  PRIMARY KEY (`id`), KEY `store_id` (`store_id`),  KEY `user_id` (`user_id`),  CONSTRAINT `f_salary_staff_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  CONSTRAINT `f_salary_staff_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE )

then f_salary

CREATE TABLE `f_salary` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `staff_id` int(11) NOT NULL,  `amount` decimal(12,2) DEFAULT NULL,  `note` tinytext,  `ref_date` date DEFAULT NULL,  `month_year` date DEFAULT NULL,  `created` datetime DEFAULT NULL,  `created_by` int(11) DEFAULT NULL,  `updated` datetime DEFAULT NULL,  `updated_by` int(11) DEFAULT NULL,  `approved` datetime DEFAULT NULL,  `approved_by` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `staff_id` (`staff_id`),  CONSTRAINT `f_salary_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `f_salary_staff` (`id`) ON DELETE CASCADE ON UPDATE CASCADE )

hope this helps those who needs to understand the table structure

i don't understand your query, "WHERE s.amount > ss.amount" can't work as s.amount doesn't exist at all — Gosfly, Feb 17 '20 at 10:58
sorry .. it should be s.paid_amount ... how can I edit the question? — Jeebsion, Feb 17 '20 at 11:04
I don't understand your tables. My assumptions: There is a `user` table and its primary key is the `user_id`. There is a `staff` table and its pk is the `staff_id`. But: How are the two related? Does the `user` table have a `staff_id` so that every user belongs to one staff? The table `f_salary` has a `staff_id` rather than a `user_id`, so a salary is on a staff not on a user? And every user in that staff gets this salary or they divide it among themselves? And then the table `f_salary_staff` has a `user_id` where the name suggest it should have a `staff_id` instead... — Thorsten Kettner, Feb 17 '20 at 11:41
... Please explain your tables. What do they represent? What are their keys? — Thorsten Kettner, Feb 17 '20 at 11:42
Hi there .. I've already put create table statement for reference .. hope that helps — Jeebsion, Feb 17 '20 at 11:53

forpas · Answer 1 · 2020-02-17T11:28:02.690

0

the selection didn't even list those with NULL values

because you have a LEFT JOIN that is turned to an INNER JOIN by the WHERE clause.
Move the condition to the ON clause.
Also in the left joined query you select the column staff_id although it is not included in the GROUP BY clause as it should.
Change to this:

SELECT ss.id AS staff_id, u.realname AS realname, ss.amount AS salary_amount, 
COALESCE(s.paid_amount, 0) AS paid_amount,
ss.amount - COALESCE(s.paid_amount, 0)
FROM f_salary_staff ss 
INNER JOIN user u ON (u.id=ss.user_id) 
LEFT JOIN (
  SELECT staff_id, ref_date, SUM(amount) AS paid_amount 
  FROM f_salary 
  WHERE month_year='2020-02-29' 
  GROUP BY staff_id, ref_date
) s ON (ss.id=s.staff_id) AND s.paid_amount > ss.amount

edited Feb 17 '20 at 11:28

answered Feb 17 '20 at 11:02

forpas

160,666
10
38
76

err .. my problem is that I wanna turn whatever NULL values resulting from the LEFT JOIN to numeric 0.00 (s.paid_amount) so I could compare it to the column ss.amount .... is it even possible to turn NULL so something numeric? – Jeebsion Feb 17 '20 at 11:11
This is what COALESCE does. All NULLs returned by the left join will be 0 in the results. – forpas Feb 17 '20 at 11:12
yes .. I did get the result as 0.00 .. however it failed to compare to column ss.amount ... have a look at the latest statement I fix .. the 5th column yields NULL :( – Jeebsion Feb 17 '20 at 11:26
tried this .. the rows with originally NULL value of s.paid_amount didn't appear :( – Jeebsion Feb 17 '20 at 11:44
The use of COALESCE does not alter the number of rows returned, it just changes NULLs to 0. Check your requirement and your code. The code in my answer is about the question as you posted it: (1)*why you don't get the rows with NULLs* and the answer is because you misuse the LEFT JOIN and (2) how to make NULLs appear as 0 and the answer is with COALESCE. – forpas Feb 17 '20 at 11:48
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207978/discussion-between-jeebsion-and-forpas). – Jeebsion Feb 17 '20 at 11:54

score 0 · Answer 2 · answered Feb 17 '20 at 11:17

0

If i understand your need well, the following query should work :

SELECT t.*
FROM (
  SELECT 
    ss.id AS staff_id, 
    u.realname AS realname, 
    ss.amount AS salary_amount, 
    COALESCE(s.paid_amount, 0) AS paid_amount 
  FROM f_salary_staff ss 
  INNER JOIN user u ON (u.id=ss.user_id) 
  LEFT JOIN (
    SELECT staff_id, ref_date, SUM(amount) AS paid_amount 
    FROM f_salary 
    WHERE month_year='2020-02-29' 
    GROUP BY staff_id, ref_date
  ) s ON (ss.id=s.staff_id) 
) t
WHERE paid_amount > salary_amount

answered Feb 17 '20 at 11:17

Gosfly

1,240
1
8
14

tried this ... didn't work .. I've edited the select statement hopefully you could understand better ... the 5th column only appear if both 3rd and 4th column is numerically legal .. else it'll be NULL – Jeebsion Feb 17 '20 at 11:22
What's the problem with my query compared to your need ? What did i missed ? – Gosfly Feb 17 '20 at 11:25
Gosfly .. that statement yields nothing :( ... I suspect COALESCE only put the 0 for show instead for it to be comparable ... you have any other idea? – Jeebsion Feb 17 '20 at 11:34
Does the subquery return you something ? – Gosfly Feb 17 '20 at 11:36
Gosfly .. sorry ... which subquery? – Jeebsion Feb 17 '20 at 11:46

score -1 · Answer 3 · answered Feb 17 '20 at 12:29

Thank you to forpas and Gosfly .. apparently COALESCE or IFNULL or even IF cannot alter the NULL to anything else other than NULL or not NOT NULL .. the result is only for show but not the real value of it .. so I've changed the statement to such instead

SELECT
  ss.id AS staff_id,
  u.realname AS realname,
  ss.amount AS salary_amount,
  IFNULL(s.paid_amount,0.00) AS paid_amount,
  IF(s.paid_amount IS NULL,ss.amount,ss.amount-s.paid_amount) AS unpaid_amount
FROM f_salary_staff ss
  JOIN user u ON (u.id=ss.user_id)
  LEFT JOIN (
    SELECT staff_id,
      month_year,
      SUM(amount) AS paid_amount
    FROM f_salary
    WHERE month_year='2020-02-29'
    GROUP BY staff_id,month_year
  ) s ON (ss.id=s.staff_id)  
WHERE ss.amount != s.paid_amount OR s.paid_amount IS NULL

thank you all!

`IFNULL(s.paid_amount,0.00)` is the same as `COALESCE(s.paid_amount,0.00)` and `IF(s.paid_amount IS NULL,ss.amount,ss.amount-s.paid_amount)` is the same as `ss.amount - COALESCE(s.paid_amount, 0)` so what is the difference? Also *apparently COALESCE or IFNULL or even IF cannot alter the NULL to anything else other than NULL or not NOT NULL* is totally wrong. — forpas, Feb 17 '20 at 12:48

MySQL SELECT IFNULL then compare it numerically with other column

3 Answers3