I'm trying to extract data from the metadatavalue table, including rows
where a particular metadata_field may not exist, but my attempts using LEFT
OUTER JOINs lead to these rows being omitted.
For example:
dspace=> SELECT DISTINCT mv1.item_id, mv1.text_value AS date,
mv2.text_value as type
FROM metadatavalue AS mv1
LEFT JOIN metadatavalue AS mv2 ON mv1.item_id = mv2.item_id
LEFT JOIN metadatavalue AS mv3 ON mv1.item_id = mv3.item_id
WHERE mv1.metadata_field_id = 12 AND mv1.text_value LIKE '2007-11-12T%Z'
AND mv2.metadata_field_id = 66 AND mv2.text_value = 'Thesis'
ORDER BY mv1.item_id;
item_id | date | type
---------+----------------------+--------
163 | 2007-11-12T09:58:28Z | Thesis
164 | 2007-11-12T09:59:31Z | Thesis
165 | 2007-11-12T09:59:40Z | Thesis
166 | 2007-11-12T13:31:43Z | Thesis
168 | 2007-11-12T13:32:02Z | Thesis
169 | 2007-11-12T13:32:09Z | Thesis
172 | 2007-11-12T13:32:30Z | Thesis
(7 rows)
... but:
dspace=> SELECT DISTINCT mv1.item_id, mv1.text_value AS date,
mv2.text_value as type
FROM metadatavalue AS mv1
LEFT JOIN metadatavalue AS mv2 ON mv1.item_id = mv2.item_id
LEFT JOIN metadatavalue AS mv3 ON mv1.item_id = mv3.item_id
WHERE mv1.metadata_field_id = 12 AND mv1.text_value LIKE '2007-11-12T%Z'
AND mv2.metadata_field_id = 66 AND mv2.text_value = 'Thesis'
AND mv3.metadata_field_id = 75
ORDER BY mv1.item_id;
item_id | date | type
---------+----------------------+--------
163 | 2007-11-12T09:58:28Z | Thesis
(1 row)
If I had null values corresponding to metadata_field_id = 75, I would have
got all my records, but where there is no metadata_field_id 75, I get zero
rows.
Is there a way to do this?
Sean
--
------------------------------------------------------------------------------
_______________________________________________
DSpace-tech mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-tech
List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
