Hi,
Doing a join on one-to-many relations (like "orders" joining "custumors") is
easy, but what if there are many-to-many relations involved?
Consider this scenario of three (simplified) tables:
people
- id
- name
accounts
- id
- owner_id REFERENCES people
account_co_owners
- co_owner_id REFERENCES people
- account_id REFERENCES accounts
I need a query that allows the user to search for accounts by giving names of
either co-owners or owners. Currently, the query responsible is this:
SELECT DISTINCT ON (account.id) account.*
FROM accounts AS account
INNER JOIN people AS owner
ON owner.id = account.owner_id
OR owner.id IN (SELECT co_owner_id
FROM account_co_owners
WHERE account_id = account.id
AND co_owner_id = owner.id)
WHERE owner.name LIKE '%user supplied search string%';
But this query is too slow for my taste. It takes about 3 seconds, for only 800
accounts). Without the subselect in the JOIN statement (and therefor without
the ability to search based on the co-owner names), it is significantly
faster.
My question is, can joining many-to-many relations be done in a better way than
what I'm doing here?
Thanks in advance.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
