php - 多重查询从两个表中获得相同的结果

Question

我有 2 个表 product_sold 和 product_received。我在两个表中有共同的字段产品销售表

产品 ID |引用|售出日期
1 |对于设计| 2017-02-01
2 | dshdshf | 2017-02-05

product_received 表

产品 ID |引用|收到的日期
4 |库存更新 | 2017-03-01
2 |股票评论 | 2017-03-05
1 |库存出货| 2017-03-06


我想要得到这样的结果

产品代码 |引用|日期
19毫米阿利明|库存出货| 2017-03-06
19毫米阿利明|对于设计| 2017-02-01
铝胶带 SC |库存回顾| 2017-03-06
铝胶带 SC | dshdshf | 2017-02-05
9mm 板材 |库存更新 | 2017-03-01

产品名称位于我的产品表中，我必须将其与这两个表连接起来。我尝试使用 mysqli_multi_query 执行查询，但没有成功。

我的产品表

产品 ID |产品名称
1 | 19毫米阿利敏
2 |铝胶带 SC
3 | 10.5毫米
4 | 9mm板材

Answer 1

两个查询可以获得您预期的结果。您需要 JOIN 和 UNION ALL 来组合两个查询。

SELECT 
    product.product_name AS 'Product Code'
 ,  product_sold.reference AS 'Reference'
 ,  product_sold.sold_date AS 'Date'
FROM 
 product
INNER JOIN 
 product_sold
ON
 product.product_id = product_sold.product_id

UNION ALL

SELECT 
    product.product_name AS 'Product Code'
 ,  product_received.reference AS 'Reference'
 ,  product_received.received_date AS 'Date'
FROM 
 product
INNER JOIN 
 product_received
ON
 product.product_id = product_received.product_id

或者

SELECT 
    product.product_name AS 'Product Code'
 ,  product_sold.reference AS 'Reference'
 ,  product_sold.sold_date AS 'Date'
FROM 
 product
INNER JOIN 
 product_sold
USING product_id

UNION ALL

SELECT 
    product.product_name AS 'Product Code'
 ,  product_received.reference AS 'Reference'
 ,  product_received.received_date AS 'Date'
FROM 
 product
INNER JOIN 
 product_received
USING product_id