mysql - 使用子查询左连接三个表

Question

Check this fiddle for DB and tables

SELECT p.*
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id
LEFT JOIN Offers o
ON ot.offer_id =  (SELECT id FROM Offers 
                   Where dF<=3 AND dt>=3)

ORDER BY o.id DESC, 
          p.prod_id ASC
LIMIT 20

输出是:

| PROD_ID | CATEGORY_ID | PROD_NAME |  BRAND | PRICE | STATUS |
---------------------------------------------------------------
|      p3 |          c1 |     prod3 | brand3 |  3000 | active |
|      p3 |          c1 |     prod3 | brand3 |  3000 | active |
|      p1 |          c1 |     prod1 | brand1 |  1000 | active |
|      p2 |          c2 |     prod2 | brand2 |  2000 | active |
|      p4 |          c2 |     prod4 | brand1 |  4000 | active |
|      p5 |          c1 |     prod5 | brand2 |  5000 | active |
|      p6 |          c2 |     prod6 | brand3 |  6000 | active |

但是，如您所见，p3 显示了两次。我不知道为什么..我已经尝试了所有可能的方法，但我找不到任何补救措施..:(

Answer 1

问题在

LEFT JOIN Offers o
ON ot.offer_id =  (SELECT id FROM Offers 
                   Where dF<=3 AND dt>=3)

您使用常量值加入 ot.offer_id(您从优惠表中选择它们没有任何意义)。

正确的做法:

SELECT p.*
FROM Products p
LEFT JOIN offertran ot 
ON p.prod_id = ot.prod_id
LEFT JOIN Offers o
ON ot.offer_id = o.id AND o.dF<=3 AND o.dt>=3
ORDER BY o.id DESC, 
          p.prod_id ASC
LIMIT 20

看this SQLFiddle