Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Message-ID: <50644DE4.3060301@medav.de>
Date: Thu, 27 Sep 2012 15:00:20 +0200
From: Tino Schmidt <tino.schmidt@medav.de>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:10.0.7)
 Gecko/20120913 Thunderbird/10.0.7
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Question regarding SubSelect Performance
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hello,

I have a problem with the following issue: I have two (three) tables, one=
 contains documents (document_table) and another one (document_attribute_=
table) contains attributes for these documents (The third table contains =
the definition of the attributes). Now i want to sort documents regarding=
 to a specific attribute, the problem is, documents which does not have t=
his particular attribute should be considered by this too.

Simple example of the 2 tables:

document_table:
id  | name
-------------------
0   | doc1
1   | doc2
-------------------

document_attribute_table:
id  | referring_document    | referring_attribute   | attribute_value
-----------------------------------------------------------------------
0   | 0                     | 1                     | value1
1   | 0                     | 2                     | value2
2   | 1                     | 1                     | value3
-----------------------------------------------------------------------

The following select statement does what is intended:

SELECT * FROM document_table LEFT JOIN (SELECT * FROM document_attribute_=
table WHERE referring_attribute=3D2) alias on alias.id=3Ddocument_table.i=
d order by alias.attribute_value

However this statement is horribly slow - now i'm looking for a "better" =
solution to this problem.