一個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 )