CODEWITHSUNDEEP
mysqlsql
user1300585
user1300585

Reputation:

MySQL, get data from two related tables if second table not always have matching rows

Example table content

'main'
| id | total |
| 1  | 10    |
| 2  | 20    |
'timed'
| id | id_main | date_from  | date_to    | total |
| 1  | 2       | 2012-03-29 | 2012-04-29 | 50    |

Desired result

| id | total |
| 1  | 10    |
| 2  | 50    |

Not exactly working query

SELECT main.id AS id, COALESCE(timed.total, main.total) AS total
FROM main
LEFT JOIN timed
ON main.id = timed.id_main
WHERE SYSDATE() BETWEEN timed.date_from AND timed.date_to

Result

| id | total |
| 2  | 50    |

In tables 'main' and 'timed' 'total' field will never be NULL. In some 'timed' records there will be no relative 'id_main', or there will be few, but they will differ, 'date_from' 'date_to' never intersect. Table 'main' is large, but in 'timed' will always be two or three relative records.

CREATE TABLE `main` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `total` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `main` VALUES (1,10);
INSERT INTO `main` VALUES (2,20);
CREATE TABLE `timed` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_main` int(11) unsigned NOT NULL DEFAULT '0',
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `total` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `link` (`id_main`)
) ENGINE=InnoDB;
INSERT INTO `timed` VALUES (1,2,'2012-03-29','2012-03-30',50);
ALTER TABLE `timed`
ADD CONSTRAINT `link` FOREIGN KEY (`id_main`) 
REFERENCES `main` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Sorry for my english.

Upvotes: 2

Views: 314

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

You should move the date condition in the join condition:

SELECT main.id AS id, COALESCE(timed.total, main.total) AS total
FROM main
LEFT JOIN timed
ON main.id = timed.id_main and SYSDATE() BETWEEN timed.date_from AND timed.date_to

In your query, those rows not matched are filtered out by the WHERE condition because timed.date_form and timed.date_to are null, so sysdate can't be between them :)

Upvotes: 1

Related Questions