SQL join that displays left table even if right table doesn't match where

Question

I have 2 tables, defined as such:

CREATE TABLE `product` (
`pid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR( 50 ) NOT NULL,
`description` TEXT,
`qty` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT '0',
`category` ENUM( '1', '2', '3', '4', '5', '6', '7', '8' ) NOT NULL DEFAULT '1',
`price` DECIMAL( 7, 2 ) UNSIGNED NOT NULL
) ENGINE = InnoDB;

CREATE TABLE `discount` (
`did` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`pid` SMALLINT( 5 ) UNSIGNED NOT NULL,
`sDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`eDate` DATETIME NOT NULL,
`dPrice` DECIMAL( 7, 2 ) UNSIGNED NOT NULL,
FOREIGN KEY ( `pid` ) REFERENCES `product`(`pid`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

I am trying to get a result of exactly 1 row for every product and a NULL dPrice or the dPrice if there's a discount entry with sDate < NOW() and eDate > NOW().

I tried:

select p.pid, name, price, dPrice, qty
from product p left join discount d
on p.pid = d.pid
where d.sDate<now() and d.eDate>now();

The problem with this is it returned only products with a valid discount. Products with no discount or expired / future discounts are not shown.

Next I tried:

select p.pid, name, price, dPrice, qty
from product p left join discount d
on p.pid = d.pid
where (d.sDate<now() and d.eDate>now()) or dPrice is null;

This came 1 step closer to my desired result, where it listed products with valid discounts and products with no discounts, but I'm still missing the products with expired / future discounts defined.

Checking that only 1 discount is active at any time is done in PHP and need not be included in this statement. Any help is much appreciated!

Answer 1

You probably want a subquery to return a filtered version of discount that you can then left join with product.

select p.pid, name, price, dPrice, qty
from product p left join
(select * from discount where sDate<now() and eDate>now()) d
on p.pid = d.pid;

(There may be a slight error in the SQL syntax here, but you get the idea: since you only want the WHERE clause to apply to one table, you apply it to that table in a subquery and then join the result data set, instead of joining the tables first and then filtering.)

Answer 2

select p.pid, name, price, dPrice, qty
from product p 

left join discount d on p.pid = d.pid and d.sDate<now() and d.eDate>now()

More efficient and more "standard" than a subquery.

Answer 3

What about

SELECT p.name, d.dPrice
FROM   product p LEFT JOIN discount d
ON     p.pid = d.pid AND now() BETWEEN d.sDate AND d.eDate

Answer 4

That's exactly what Outer Joins have been invented for.

select p.*,d.dPrice from product p left outer join discount d on p.pid=d.pid and now() between d.sDate and d.eDate

You can use abbreviation left join instead of full left outer join. However, it's still good to remember that they are outer joins as opposed to usual inner joins.