Retrieve data from tables where one has multiple columns pointing to the same primary key in the other?

Question

I'm not responsible for this design, but I've have to extract data from this schema that looks something like this (SQL Server 2000):

CREATE TABLE contract
(
 contract_id int,
 account_id int,    /* account table */
 responsible_id int,   /* account table */
 holding_id int,    /* account table */
 billingaddress_id int,  /* address table */
 deliveryaddress_id int,  /* address table */
)

CREATE TABLE address
(
 address_id int,
 postalcode char(4),
)

CREATE TABLE account
(
 account_id int,
 firstname varchar(40),
 billingaddress_id int,  /* address table */
 deliveryaddress_id int,  /* address table */
)

The account_id, responsible_id and holding_id on the contracts table can be null, share values, or have different values. It may or may not have a billing and/or delivery address. An account entity always has a billing or delivery address, and both can be the same.

I have to find all the accounts associated with contracts (ie. a contract has the same account, responsible or holding ID as the account id), and are associated with addresses that have a certain postal code (either directly, or through the contract).

The problem seems to be 2-fold:
a) Retrieving accounts associated with contracts b) Filtering the results from (a) to get accounts associated with a certain postal code

This doesn't work because if the account_id is not associated with the postal code in question but the holding_id is, then it won't get returned:

FROM account
INNER JOIN contract
 ON account.account_id = 
  CASE WHEN NOT IsNull(contract.account_id) THEN contract.account_id 
  WHEN NOT IsNull(contract.responsible_id) THEN contract.responsible_id 
  ELSE contract.holding_id END

This is far too slow for some reason (FK's are not indexed - waited for 30 mins and it didn't return):

FROM account
INNER JOIN contract
 ON account.account_id = contract.account_id
 OR account.account_id = contract.responsible_id
 OR account.account_id = contract.holding_id

The only thing that seemed to have worked was a UNION, but then I'm still left with the problem of filtering the results by address type.

What is the shortest method of returning the required results? At the moment I'm leaning toward creating a temporary table to store intemediary data.

Answer 1

SELECT  *
FROM    contract
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    account
        JOIN    address
        ON      address_id IN (billingaddress_id, deliveryaddress_id)
        WHERE   account_id IN (account_id, responsible_id, holding_id)
                AND postalcode = @mycode
        )

To select accounts, use this:

SELECT  *
FROM    account ao
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    (
                SELECT  account_id, responsible_id, holding_id
                FROM    contract c
                WHERE   c.account_id = ao.account_id
                UNION ALL
                SELECT  account_id, responsible_id, holding_id
                FROM    contract c
                WHERE   c.responsible_id = ao.account_id
                UNION ALL
                SELECT  account_id, responsible_id, holding_id
                FROM    contract c
                WHERE   c.holding_id = ao.account_id
                ) co
        JOIN    account ai
        ON      ai.account_id IN (co.account_id, co.responsible_id, co.holding_id)
        JOIN    address
        ON      address_id IN (billingaddress_id, deliveryaddress_id)
        WHERE   postalcode = @mycode
        )

Update:

Since you columns are not indexed, EXISTS will not be efficient in this case, since it's not rewriteable as an IN.

You should rewrite all your join conditions to be equijoins so that HASH JOIN method is usable.

Try this:

SELECT  a.account_id
FROM    (
        SELECT  account_id
        FROM    contract
        UNION
        SELECT  responsible_id
        FROM    contract
        UNION
        SELECT  holding_id
        FROM    contract
        ) c
JOIN    (
        SELECT  account_id, billingaddress_id AS address_id
        FROM    account
        UNION
        SELECT  account_id, deliveryaddress_id
        FROM    account
        ) a
ON      a.account_id = c.account_id
JOIN    address ad
ON      ad.address_id = a.address_id
WHERE   ad.postalcode = @mycode

Answer 2

In the end, I settled for something like this:

FROM (
SELECT Account.*
FROM   (SELECT Contract.Account_Id          AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Account_Id          AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Account_Id          AS ForeignKey_Id,
               Contract.BillingAddress_Id AS Address_Id
        FROM   Contract) ContractInfo
       JOIN Account Account
         ON Account.Name_Id = ForeignKey_Id
       JOIN Address
         ON Address.Address_Id = ContractInfo.Address_Id
            AND Address.PostalCode = 'ABCDE'
UNION
SELECT Account.*
FROM   (SELECT Contract.Responsible_Id        AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Responsible_Id        AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Responsible_Id        AS ForeignKey_Id,
               Contract.BillingAddress_Id AS Address_Id
        FROM   Contract) ContractInfo
       JOIN Account Account
         ON Account.Name_Id = ForeignKey_Id
       JOIN Address
         ON Address.Address_Id = ContractInfo.Address_Id
            AND Address.PostalCode = 'ABCDE'
UNION
SELECT Account.*
FROM   (SELECT Contract.Holding_Id     AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Holding_Id     AS ForeignKey_Id,
               Contract.DeliveryAddress_Id AS Address_Id
        FROM   Contract
        UNION
        SELECT Contract.Holding_Id    AS ForeignKey_Id,
               Contract.BillingAddress_Id AS Address_Id
        FROM   Contract) ContractInfo
       JOIN Account Account
         ON Account.Name_Id = ForeignKey_Id
       JOIN Address
         ON Address.Address_Id = ContractInfo.Address_Id
            AND Address.PostalCode = 'ABCDE'
) Account

It performs better than using per-row subselects or the IN clause.