SQL giving me headache - multiple joins

First I开发者_运维知识库'll explain my scenario. Here's the 2 general tables in use:

Accounts:

LegacyID = The identifer of the account

InvoiceAccount = An account can have multiple subaccounts in the same table. This field is the LegacyID of its parent account

Customer = There can be two different types of accounts, and they can share ID's - so this column differentiates between the 2. For the purposes of my SQL I always want it to be true rather than false

AllocatedUser = The username of the person who needs to see this deliveries. This is only populated on the parent account, so I need to link back to get this for the subaccounts

Deliveries:

LegacyID = The deliveries identifier

Customer = The LegacyID of the account related to the delivery (can be a subaccount)

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

Now that's explained, basically I need an SQL which returns any deliveries that are 'OnHold', but only for deliveries for accounts that are allocated the logged in user. The query for selecting the AllocatedUser if the delivery links to a parent account was simple, but I'm having issues with returning rows if the delivery is linked to a subaccount - it's simply not returning any. Here is the SQL below:

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)

My mind is frazzled from trying to work out why it don't work at the moment - I'd really appreciate any advice.

Thanks!

It sounds like you're looking for records in the Deliveries table that are associated with a user's account, the user's direct parent's account, and the user's direct child's account. If this is true modifying your query as follows should do the trick.

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

First comment is that you should build the joins in steps if you are having issues.

Second comment is that in the query you posted there is unnecessary join - you are not filtering nor selecting nor joining on anything specific from table aliased as Account_1.

Maybe that is an indication of what you are doing wrong. Otherwise, the query looks(!) ok (assuming that all the joins are correct; your descriptions are not exact - for example for Accounts.Customer you say that 'For the purposes of my SQL I always want it to be true rather than false' - well the Customer is not true/false field is it? What do you mean? There are other such inconsistencies...)

Some sample data and a sample of the result could shed some more light.

 ON Deliveries_1.Customer = Account_1.InvoiceAccount
 ON Account_1.InvoiceAccount = Account_2.LegacyID 

If both of these criteria are true, then Delivery.Customer would equal Account2.LegacyId (delivery would have to be on the parent account). I don't think that's what you intended.

Perhaps you meant

 ON Deliveries_1.Customer = Account_1.LegacyID
 ON Account_1.InvoiceAccount = Account_2.LegacyID