c# - SQL 让我头疼 - 多个连接

Question

首先，我将解释我的场景。这是正在使用的 2 个通用表:

帐户:

LegacyID = The identifer of theaccount

InvoiceAccount = An account can havemultiple subaccounts in the sametable. This field is the LegacyIDof its parent account

Customer = There can be two differenttypes of accounts, and they can shareID's - so this column differentiatesbetween the 2. For the purposes of mySQL I always want it to be true ratherthan false

AllocatedUser = The username of theperson who needs to see thisdeliveries. This is only populated onthe parent account, so I need to linkback to get this for the subaccounts

交付:

LegacyID = The deliveries identifier

Customer = The LegacyID of the accountrelated to the delivery (can be asubaccount)

OnHold = A flag which for the purposesof my query needs to be 'true'

现在已经解释过了，基本上我需要一个 SQL 来返回任何“暂停”的交付，但仅限于分配给登录用户的帐户的交付。如果传递链接到父帐户，则选择 AllocatedUser 的查询很简单，但如果传递链接到子帐户，我会遇到返回行的问题 - 它根本不返回任何内容。下面是 SQL:

SELECT     Deliveries_1.LegacyID, Deliveries_1.TripDate, Deliveries_1.OnHoldReason, Account_2.AllocatedUser
FROM         Deliveries AS Deliveries_1 INNER JOIN
                      Account AS Account_1 ON Deliveries_1.Customer = Account_1.InvoiceAccount INNER JOIN
                      Account AS Account_2 ON Account_1.InvoiceAccount = Account_2.LegacyID
WHERE     (Deliveries_1.OnHold = @OnHold) AND (Account_2.Customer = 'True') AND (Account_2.AllocatedUser = @AllocatedUser)

我的思绪因试图弄清楚为什么它现在不起作用而感到疲倦 - 我真的很感激任何建议。

谢谢!

Answer 1

听起来您正在 Deliveries 表中查找与用户帐户、用户的直系父级帐户和用户的直系子级帐户关联的记录。如果这是真的，按如下方式修改您的查询应该可以解决问题。

SELECT DISTINCT d.LegacyID, d.TripDate, d.OnHoldReason,
       case
          when child.LegacyID = d.Customer then child.AllocatedUser
          when parent.LegacyID = d.Customer then parent.AllocatedUser
          else this.AllocatedUser
       end as 'Delivery_AllocatedUser'
FROM Account this
  LEFT JOIN Account parent on parent.LegacyID = this.InvoiceAccount
  LEFT JOIN Account child on this.LegacyID = child.InvoiceAccount
  JOIN Deliveries d on (d.Customer = this.LegacyID AND this.Customer = 'True')
                    OR (d.Customer = parent.LegacyID AND parent.Customer = 'True')
                    OR (d.Customer = child.LegacyID AND child.Customer = 'True')
WHERE d.OnHold = @OnHold
  AND this.AllocatedUser = @AllocatedUser