1

I'm given a MySQL database with tables like this:

show create table c;
CREATE TABLE `c` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  -- and other columns
  PRIMARY KEY (`id`)
)
show create table c_r;
CREATE TABLE `c_r` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `c_date` date DEFAULT NULL,
  `something_numeric` decimal(8,3) DEFAULT NULL,
  -- and other columns
  PRIMARY KEY (`id`),
  CONSTRAINT ... FOREIGN KEY (`c_id`) REFERENCES `c` (`id`)
)
show create table c_s;
CREATE TABLE `c_s` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_id` int(11) DEFAULT NULL,
  `c_date` date DEFAULT NULL
  `something_numeric` decimal(8,3) DEFAULT NULL,
  -- and other columns
  PRIMARY KEY (`id`),
  CONSTRAINT ... FOREIGN KEY (`c_id`) REFERENCES `c` (`id`)
)

Now, I want, for each date, the sum of the something_numeric values from c_r and the sum of the something_numeric values from c_s. If one of those is null, I still want the other. My output should have a row per date.

I don't see a way to get that. If I join twice on c.id, I get only such dates as exist in both c_r and c_s; so I need to left join twice instead. But then how do I make sure to get one row per date altogether, whether that date exists in only one table, only the other, or both?

10
  • 1
    Looks like the IFNULL() function is what you need.
    – mustaccio
    Jul 20 '17 at 13:51
  • Do you mean you want the output to be: date, sum(c_r.something_numeric), sum(c_s.something_numeric) or date, sum(something_numeric)?
    – Marco
    Jul 20 '17 at 13:53
  • The former, @Marco.
    – msh210
    Jul 20 '17 at 13:54
  • @mustaccio, something like select ifnull(c_r.date,c_s.date), sum(c_r.something_numeric), sum(c_s.something.numeric) from c left join c_r on c_r.c_id=c.id left join c_s on c_s.c_id=c.id? That'll get the wrong data for dates where one of the dates is null. I want the c_r.something_numeric output to be empty where c_r.date is null. If you meant something else, please clarify (presumably in an answer post).
    – msh210
    Jul 20 '17 at 13:56
  • Your question states: "If one of those [something_numeric] is null, I still want the other"; why do you then use dates in the NULLIF() call?
    – mustaccio
    Jul 20 '17 at 13:59
4

Without an actual query attempt from you, some thing are difficult to understand.

From what you've said, you're looking to group by date only, not on any information from c. That being the case, it's not clear that c needs to be in the query at all.

The following query should give you the totals you want, for all dates found in either c_r or c_s:

SELECT c_date
      ,COALESCE(SUM(r_amount),0) as r_amount
      ,COALESCE(SUM(s_amount),0) as s_amount
  FROM (
        SELECT c_date, 0 as r_amount, something_numeric as s_amount
          FROM c_s
        UNION ALL
        SELECT c_date, something_numeric as r_amount, 0 as s_amount
          FROM c_r
       ) sq
 GROUP BY c_date
;

If you need to limit rows by some value in c, INNER JOIN each of the tables in the subquery to c separately, with identical WHERE clauses. You could alternately include the values from c in the subquery's SELECT list, and filter in the main query, but that's probably less efficient.

1
  • +1, looks promising; many thanks; I'll try it when I get back to this next week.
    – msh210
    Jul 20 '17 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.