SQL relate 4一对多表

Question

我有以下查询，它没有给出我想要的输出：

   SELECT DBO.[PLAN].DATE, 
       DBO.TBRECEIPT.RECEIPTDATE, 
       DBO.TBRECEIPTLINE.RECEIPTQTY, 
       DBO.PLAN_LINE.PALLETS 
FROM   DBO.TBRECEIPT 
       INNER JOIN DBO.TBRECEIPTLINE 
               ON DBO.TBRECEIPT.PKID = DBO.TBRECEIPTLINE.RECEIPTID 
       INNER JOIN DBO.[PLAN] 
                  INNER JOIN DBO.PLAN_LINE 
                          ON DBO.[PLAN].PKID = DBO.PLAN_LINE.PLANID 
               ON DBO.TBRECEIPT.RECEIPTDATE = DBO.[PLAN].DATE 

我一定是不正确地关联了这两组标题/行表。

有人能发现这个错误吗？

谢谢

Answer 1

SELECT DBO.[PLAN].DATE, 
       DBO.TBRECEIPT.RECEIPTDATE, 
       DBO.TBRECEIPTLINE.RECEIPTQTY, 
       DBO.PLAN_LINE.PALLETS 
FROM   DBO.TBRECEIPT 
       INNER JOIN DBO.TBRECEIPTLINE 
               ON DBO.TBRECEIPT.PKID = DBO.TBRECEIPTLINE.RECEIPTID 
       INNER JOIN DBO.[PLAN] 
                  --No Condition? Problem 
                  INNER JOIN DBO.PLAN_LINE 
                          ON DBO.[PLAN].PKID = DBO.PLAN_LINE.PLANID 
               ON DBO.TBRECEIPT.RECEIPTDATE = DBO.[PLAN].DATE 

试试这个:我将建议您使用别名作为表，以获得更好的代码实践。

SELECT P.DATE, 
       R.RECEIPTDATE, 
       RL.RECEIPTQTY, 
       PL.PALLETS 
FROM   DBO.TBRECEIPT R 
       INNER JOIN DBO.TBRECEIPTLINE RL 
               ON R.PKID = RL.RECEIPTID 
       INNER JOIN DBO.[PLAN] P 
               ON R.RECEIPTDATE = P.DATE 
       INNER JOIN DBO.PLAN_LINE PL 
               ON P.PKID = PL.PLANID