一个mysql联表查询的问题

问题描述

stock(进货单表)stock_id -- 进货单ID主键stock_product(进货单和产品关系表)stock_id -- 进货单IDproduct_sn -- 产品编号(厂家提供的编号，每个产品一个)product_no -- 产品自编号(针对该进货单的每个产品的自编号，同一进货单内自编号和产品编号是一一对应，但不同进货单里可能有相同的自编号对应不同的厂家产品编号)quantity -- 进货数量shipment(出货单表)shipment_id -- 出货单IDstock_id -- 进货单IDshipment_product(出货单和产品关系表)shipment_id -- 出货单IDproduct_no -- 产品自编号quantity -- 出货数量

# 查询进货单ID为1的产品出货数量明细SELECT sh.stock_id, st_p.product_sn, sh_p.quantityFROM shipment_product sh_p LEFT JOIN shipment sh ON sh.shipment_id=sh_p.shipment_id LEFT JOIN stock_product st_p ON st_p.product_no=sh_p.product_no WHERE sh.stock_id=1

这个SQL查出来的出货情况是不对的，请问该怎么写？

问题解答

SELECT *FROM shipment_product shipment_productLEFT JOIN shipment shipment ON shipment.shipment_id = shipment_product.shipment_idWHERE shipment_product.product_no IN (SELECT stock_product.product_noFROM stock_product stock_productLEFT JOIN stock stock ON stock.stock_id = stock_product.stock_idWHERE stock_product.stock_id = 1 )回答2：

第二个left join stock表试试吧

如果 stock_product表中的字段product_no等于shipment_product表

SELECT *FROM shipment_product AS sh_pWHERE product_no = ( SELECT stock_id FROM stock_product AS st_p WHERE st_p.stock_id = 1 )

不一样的话

SELECT *FROM shipment_product AS sh_pWHERE sh_p.shipment_id = ( SELECT shipment_id FROM shipment AS st_p WHERE st_p.stock_id = 1 )