Hi,
I do not fully understand your query because you are joining
document_table.id on 'alias.id' ?
Given your columns names and your sample data set, I'd think you
should join on alias.referring_document ? (the document_attribute_table
is a N--N association intermediate table, isn't it ?
The document_attribute_table.id column would be superfluous ?)
In this case, why not a simple left join :
SELECT * FROM document_table doc
LEFT JOIN document_attribute_table da
ON da.referring_document = doc.id
WHERE da.referring_attribute = 2
OR da.referring_attribute IS NULL
ORDER BY da.attribute_value -- do NULL go first or last ?
Regarding performances, I observed that you should either declare
your foreign key constraints to Derby, or create an index on
da.referring_document to speed up the join.
HTH...
Le 27/09/2012 15:00, Tino Schmidt a écrit :
> 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
this 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=2) alias on alias.id=document_table.id order by alias.attribute_value
>
> However this statement is horribly slow - now i'm looking for a "better" solution to
this problem.
>
>
|