From: Wiebe Cazemier <halfgaar@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Joins on many-to-many relations.
Date: Fri, 16 Mar 2007 12:56:02 +0100
Message-ID: <ete0ki$l9s$1@sea.gmane.org> (raw)
References: <et94vu$qdt$1@sea.gmane.org>
<5.2.1.1.0.20070314133826.050002b0@pop6.sympatico.ca>
<et9r79$kj5$1@sea.gmane.org>
On Wednesday 14 March 2007 22:59, Wiebe Cazemier wrote:
> My question was mainly general;
> if there is a better way than using subselects to join two tables which
> are only connected to eachother through a join-table (containing only
> references to the two tables in question). Subselects are usually very
> slow, aren't they?
I fixed it. I now have a query with two outer joins, instead of the subselect
in the join condition:
SELECT DISTINCT ON (account.id) account.*
FROM trade.transaction_accounts AS account
INNER JOIN people.people AS owner
ON owner.id = account.owner_id
LEFT OUTER JOIN trade.transaction_account_co_owners acct_co_owner
ON account.id = acct_co_owner.account_id
LEFT OUTER JOIN people.people AS co_owner
ON acct_co_owner.co_owner_id = co_owner.id
WHERE upper(account.description) LIKE '%KOE%'
OR upper(owner.name) LIKE '%KOE%'
OR upper(owner.familiar_name) LIKE '%KOE%'
OR upper(owner.full_name) LIKE '%KOE%'
OR upper(co_owner.name) LIKE '%KOE%'
OR upper(co_owner.familiar_name) LIKE '%KOE%'
OR upper(co_owner.full_name) LIKE '%KOE%'
And now it executes in 1 ms.
This is what I was trying to do from the beginning, but because of some mental
block, was unable to think of the join condition...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: halfgaar@gmx.net
Subject: Re: Joins on many-to-many relations.
In-Reply-To: <ete0ki$l9s$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox