CODEWITHSUNDEEP
mysqlsql
user3663131
user3663131

Reputation: 23

MySQL Join even if there is no matching data

I am working to join the contents of my table, even when data is missing for some columns in the Join. I got it to work partially but the data where the one column is missing data to join with is swapped.

I've included my test table, row inserts and query I am working with.

--Test Table

CREATE TABLE IF NOT EXISTS `TestTable` (
  `ID` int(11) NOT NULL,
  `Data` int(4) NOT NULL,
  `LastModified` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--Test Table Data

INSERT INTO `TestTable` (`ID`, `Data`, `LastModified`) VALUES
(1, 2480, '2014-05-20 00:00:00'),
(1, 2500, '2014-05-21 00:00:00'),
(2, 4560, '2014-05-20 00:00:00'),
(2, 8542, '2014-05-21 00:00:00'),
(3, 4587, '2014-05-21 00:00:00'),
(4, 2561, '2014-05-20 00:00:00'),
(4, 2561, '2014-05-21 00:00:00');

--Table Contents

ID  Data    LastModified
1   2480    2014-05-20 00:00:00
1   2500    2014-05-21 00:00:00
2   4560    2014-05-20 00:00:00
2   8542    2014-05-21 00:00:00
3   4587    2014-05-21 00:00:00
4   2561    2014-05-20 00:00:00
4   2561    2014-05-21 00:00:00

--Query I'm working with

SELECT `t1`.`ID`, `t1`.`Data`, `t1`.`LastModified`, `t2`.`ID`, `t2`.`Data`, `t2`.`LastModified`
FROM `TestTable` AS `t1`
LEFT JOIN `TestTable` AS `t2`
ON (`t1`.`ID` = `t2`.`ID`)
AND DATE(`t1`.`LastModified`) = '2014-05-20'
AND DATE(`t2`.`LastModified`) = '2014-05-21'
GROUP BY `t1`.`ID`
ORDER BY `t1`.`ID` ASC

--Query Results

ID  Data    LastModified    ID  Data    LastModified
1   2480    2014-05-20 00:00:00 1   2500    2014-05-21 00:00:00
2   4560    2014-05-20 00:00:00 2   8542    2014-05-21 00:00:00
3   4587    2014-05-21 00:00:00 NULL    NULL    NULL
4   2561    2014-05-20 00:00:00 4   2561    2014-05-21 00:00:00

--Results I'm trying to get, columns switched for ID 3

ID      Data    LastModified        ID  Data    LastModified
1       2480    2014-05-20 00:00:00 1   2500    2014-05-21 00:00:00
2       4560    2014-05-20 00:00:00 2   8542    2014-05-21 00:00:00
NULL    NULL    NULL                3   4587    2014-05-21 00:00:00
4       2561    2014-05-20 00:00:00 4   2561    2014-05-21 00:00:00

Upvotes: 1

Views: 5448

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Your problem is that you want to keep all the ids and then look at the matches on the two days. To do such a query, start with all the ids and then use left join to match to each condition:

SELECT `t1`.`ID`, `t1`.`Data`, `t1`.`LastModified`,
       `t2`.`ID` as id2, `t2`.`Data` as data2, `t2`.`LastModified` as LastModified2
FROM (select distinct id from TestTable) ids left outer join
     `TestTable` `t1`
     on ids.id = t1.id and DATE(`t1`.`LastModified`) = '2014-05-20' left outer join
     `TestTable` `t2`
     on ids.`ID` = `t2`.`ID` and DATE(`t2`.`LastModified`) = '2014-05-21'
GROUP BY ids.`ID`
ORDER BY ids.`ID` ASC;

The SQL Fiddle is here.

EDIT:

SGeddes makes a really good point. The group by is unnecessary:

SELECT `t1`.`ID`, `t1`.`Data`, `t1`.`LastModified`,
       `t2`.`ID` as id2, `t2`.`Data` as data2, `t2`.`LastModified` as LastModified2
FROM (select distinct id from TestTable) ids left outer join
     `TestTable` `t1`
     on ids.id = t1.id and DATE(`t1`.`LastModified`) = '2014-05-20' left outer join
     `TestTable` `t2`
     on ids.`ID` = `t2`.`ID` and DATE(`t2`.`LastModified`) = '2014-05-21'
ORDER BY ids.`ID` ASC;

Upvotes: 3

DdW
DdW

Reputation: 918

Are you looking for a right join?

SELECT `t1`.`ID`, `t1`.`Data`, `t1`.`LastModified`, `t2`.`ID`, `t2`.`Data`, `t2`.`LastModified`
FROM `TestTable` AS `t1`
RIGHT JOIN `TestTable` AS `t2`
ON (`t1`.`ID` = `t2`.`ID`)
AND DATE(`t1`.`LastModified`) = '2014-05-20'
AND DATE(`t2`.`LastModified`) = '2014-05-21'
GROUP BY `t1`.`ID`
ORDER BY `t1`.`ID` ASC

Otherwise, if you want to show every date that appears in either t1 or t2, try a full outer join. If you want every date to show up (even ones that don't show up in any table), you need to join on an extra (temporary) table with the dates

Upvotes: 0

Related Questions