php - 如何在Mysql中实现Join来获取以下记录

Question

我是mysql新手，我有如下三个表

表 1 购买

 +--------+-----------+--------+
 | UserID | productID |  traID |
 +--------+-----------+--------+
 | 525    |    2      |  602   |
 +--------+-----------+--------+
 | 525    |   1001    |  602   |
 +--------+-----------+--------+
 | 525    |   1002    |  602   |
 +--------+-----------+--------+
 | 525    |    1      |  602   |
 +--------+-----------+--------+

表 2 优惠

 +--------+-----------+
 | dealsid| deals_name|
 +--------+-----------+
 |   1    | First Deal|
 +--------+-----------+
 |   2    |Second Deal|
 +--------+-----------+

表 3 产品主管

 +-----------+--------------+
 | productID | product_name |
 +-----------+--------------+
 | 1001      | HTML         |
 +-----------+--------------+
 | 1002      | JAVA         |
 +-----------+--------------+

我想要上表的输出如下

(第一笔交易，第二笔交易，HTML，JAVA)

where 子句可以实现为purchase.traID = 602，正如我在上表中发布的那样。

请帮我一些mysql查询。

Answer 1

试试这个，

SELECT pm.product_name , d.deals_name
FROM   purchase p, 
       deals d, 
       productmaster pm 
WHERE  p.productid = d.dealsid 
        OR p.productid = pm.productid 
       AND p.traID = 602;

您可以使用SELECT unique代替select我建议使用存储过程:从任一表中查找结果并将其并集作为输出

CREATE PROCEDURE sp_get_product_name
(
arg_traid int
)
BEGIN
    Select pm.product_name 
    from productmaster pm 
    inner join  purchase p
    on pm.productID = p.productID
    where p.traID = arg_traid
    Union
    Select d.deals_name
    from deals d
    inner join purchase p
    on d.dealsID = p.productID
    where p.traID = arg_traid

END