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.
