当前位置: 动力学知识库 > 问答 > 编程问答 >

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

问题描述:

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!

网友答案:
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.

网友答案:

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
网友答案:

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

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: