To: pgsql-sql@postgresql.org
From: Wiebe Cazemier <halfgaar@gmx.net>
Subject: Joins on many-to-many relations.
Date: Wed, 14 Mar 2007 16:39:42 +0100
Lines: 42
Message-ID: <et94vu$qdt$1@sea.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
User-Agent: KNode/0.10.4

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.