Extract data from tables that have multiple columns pointing to the same primary key in another?
I'm not responsible for this design, but I need to extract data from this schema, which 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 */
)
Account_id, responsible_id, and hold_id in the contracts table can be zero, stock values, or different values. It may or may not have a billing and / or shipping address. An account object always has a billing or shipping address, and both can be the same.
I need to find all the accounts associated with contracts (i.e. the contract has the same account, in charge or holding the ID as the account id) and linked to addresses that have a specific zip code (either directly or through a contract) ...
The problem seems to be 2x:
a) Retrieving invoices associated with contracts b) Filtering results from (a) to retrieve invoices associated with a specific zip code
This does not work because if the account_id is not associated with the specified postcode, but has_ hold_id, then it will not be 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 too slow for some reason (FK is not indexed - waited 30 minutes 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 work was UNION, but then I still have the problem of filtering the results by address type.
What is the shortest way to return the required results? At the moment I am leaning towards creating a temporary table to store the data it contains.
source to share
In the end, I solved 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 works better than using sub-segments in a string or IN clause.
source to share
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 the columns are not indexed, EXISTS
it won't be efficient in this case as it won't be overwritten as IN
.
You must rewrite all join conditions as equijoins in order to use the method HASH JOIN
.
Try the following:
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
source to share