MySQL: SELECT and JOIN with not exists values?

Question

How can i SELECT data from two tables using 'JOIN' operator, if some values with cat_idx and tar_id from categories (tar_id = 87) does not exists in time_discounts table. (I need to print '0' in this case)

See My SQL Schema:

CREATE TABLE `categories` (
 `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `tar_id` int(11) NOT NULL,
 `cat_idx` int(11) NOT NULL);
CREATE TABLE `time_discounts` (
 `dis_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `tar_id` int(11) NOT NULL,
 `cat_idx` int(11) NOT NULL,
 `discount`double NOT NULL
);
insert into categories values(null, 87, 122);
insert into categories values(null, 82, 120);
insert into categories values(null, 87, 128);
insert into categories values(null, 81, 14);
insert into categories values(null, 87, 135);
insert into categories values(null, 87, 140);
insert into time_discounts values(null, 87, 122, 2.34);
insert into time_discounts values(null, 82, 120, 9.22);
insert into time_discounts values(null, 81, 14, 2.11);
insert into time_discounts values(null, 87, 135, 9.35);
insert into time_discounts values(null, 80, 11, 83.22);

For tar_id = 87 and cat_idx = 122 from categories table we can find values from time_discounts table, but for cat_idx = 140 and 128 values does not exists in time_discounts table. In this case output should be something like this:

| tar_id | cat_idx | discount |
|--------|---------|----------|
| 87 | 122 | 2.34 |
| 87 | 128 | 0 |
| 87 | 135 | 9.35 |
| 87 | 140 | 0 |

My query:

SELECT `c`.`tar_id`, `c`.`cat_idx`, IFNULL(`td`.`discount`, 0)
FROM `categories` AS `c`
LEFT JOIN `time_discounts` AS `td`
 ON `td`.`tar_id` = `c`.`tar_id`
 AND `td`.`cat_idx` = `c`.`cat_idx`;

But i need to filter data by tar_id = 87 and if i add AND c.tar_id = 87 i also gets all values from time_discounts, not only tar_id = 87.

Please help and sorry for my english

Answer 1

Change AND c.tar_id = 87 to WHERE c.tar_id = 87.

Your soultion would work only if it was an INNER JOIN instead. With LEFT JOIN it works different. The filter is applied for the join predicate and the result is different.