MYSQL INSERT and JOIN return single result, should return multiple results

Question

Table XX

  Day-A       Day-1      Day-B      Day-2    Cat   Dog
---------- ---------- ---------- ---------- ----- -----
2018/01/12     NULL      NULL       NULL      Y     N
   NULL        NULL   2018/02/02    NULL      N     Y
   NULL        NULL   2018/01/26    NULL      N     Y
   NULL    2018/01/15    NULL       NULL      Y     N
   NULL        NULL      NULL    2018/01/15   N     Y
   NULL        NULL   2018/01/19    NULL      N     Y

I would like to match Day-A and Day-B, and Day-1 and Day-2. Day-A and Day-1 will be tagged as 'Cat', while Day-B and Day-2 will be tagged as 'Dog'. 'Cat' always matched with 'Dog'

Day-A and Day-B need to be matched by day e.g. Monday = Monday.
Day-1 and Day-2 need to be matched by same date.

I can do the Day-1 and Day-2 match, but not another one.

INSERT INTO T1 (Day-A, Day-1, Day-B, Day-2, c.Cat, d.Dog)
SELECT c.Day-A, c.Day-1, d.Day-B, d.Day-2
FROM calendar AS c
    LEFT JOIN calendar AS d
    ON ((IFNULL(c.Day-1, 'x') = IFNULL(d.Day-2, 'x')
    AND IFNULL(DAYNAME(c.Day-A), 'x') = IFNULL(DAYNAME(d.Day-B), 'x'))
    WHERE c.Cat = d.Dog

I matched Day-1 and Day-2 but not another pair. Only the closest date is showing up. In my case only 2018/01/19 is showed, but not 2018/1/26 nor 2018/02/02, though they are all Friday. Day-A and Day-B can be several months different in my table.

My desired table result:

Id     Day-A       Day-1      Day-B      Day-2    Cat    Dog
--- ---------- ---------- ---------- ----------  -----  -----
 1  2018/01/12     NULL   2018/01/19     NULL      Y      Y
 2  2018/01/12     NULL   2018/01/26     NULL      Y      Y
 3  2018/01/12     NULL   2018/02/02     NULL      Y      Y
 4     NULL    2018/01/15     NULL    2018/01/15   Y      Y

Is it possible to have more than one result for DAYNAME() match?

Edit your question to show the query you have tried, the one that only shows one match on Day-A and Day-B. Also, edit your question to show the table schema/name — Sloan Thrasher, Jan 20 '18 at 19:25
@SloanThrasher Thank you for correcting me. Now it is better for everybody to read it. — Chicken Maker, Jan 20 '18 at 19:42

score 2 · Accepted Answer · answered Jan 20 '18 at 20:09

You're actually quite close. I built an SQLFiddle and populated it with your data. The query you gave above produced the following result:

Day_A       Day_1       Day_B       Day_2       Cat    Dog
(null)      (null)      (null)      (null)      N      N
(null)      (null)      (null)      (null)      N      N
(null)      (null)      (null)      (null)      N      N
(null)      (null)      (null)      (null)      N      N
2018-01-12  (null)      2018-02-02  (null)      Y      Y
2018-01-12  (null)      2018-01-26  (null)      Y      Y
(null)      (null)      (null)      (null)      N      N
(null)      (null)      (null)      (null)      N      N
(null)      (null)      (null)      (null)      N      N
(null)      (null)      (null)      (null)      N      N
(null)      2018-01-15  (null)      2018-01-15  Y      Y
2018-01-12  (null)      2018-01-19  (null)      Y      Y

At first glance this looks nothing like the desired results, until you realize that the only difference is all the lines with NULL values in DAY_A, DAY_1, DAY_B, and DAY_2. So if we eliminate the rows where DAY_A, DAY_1, DAY_B, and DAY_2 are all NULL we should be good:

SELECT c.Day_A, c.Day_1, d.Day_B, d.Day_2, c.Cat, d.Dog
  FROM calendar AS c
  LEFT JOIN calendar AS d
    ON IFNULL(c.Day_1, 'x') = IFNULL(d.Day_2, 'x') AND
       IFNULL(DAYNAME(c.Day_A), 'x') = IFNULL(DAYNAME(d.Day_B), 'x')
  WHERE c.Cat = d.Dog AND
        (c.DAY_A IS NOT NULL OR
         c.DAY_1 IS NOT NULL OR
         d.DAY_B IS NOT NULL OR
         d.DAY_2 IS NOT NULL)

which produces

Day_A       Day_1       Day_B       Day_2       Cat    Dog
2018-01-12  (null)      2018-02-02  (null)      Y      Y
2018-01-12  (null)      2018-01-26  (null)      Y      Y
(null)      2018-01-15  (null)      2018-01-15  Y      Y
2018-01-12  (null)      2018-01-19  (null)      Y      Y

which seems to be what was wanted.

Best of luck.

All you have to do is add the INSERT part of your query to this select, then it should work as you asked. — Sloan Thrasher, Jan 20 '18 at 21:56
I found my mistake of setting wrong primary key, thus the INSERT only insert 1 value. — Chicken Maker, Jan 20 '18 at 22:24

MYSQL INSERT and JOIN return single result, should return multiple results

1 Answers1