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

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 2

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 3

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 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.

友情链接