On 31 March 2014 14:35, Sean Carte <[email protected]> wrote:
> 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?
>
SELECT t1.item_id, t1.date, t1.type, t2.pub_num FROM (
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'
) t1
LEFT JOIN
( SELECT DISTINCT mv1.item_id, mv1.text_value AS date, mv2.text_value as
type, mv3.text_value AS pub_num
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
) t2
ON t1.item_id = t2.item_id;
item_id | date | type | pub_num
---------+----------------------+--------+------------
164 | 2007-11-12T09:59:31Z | Thesis |
165 | 2007-11-12T09:59:40Z | Thesis |
169 | 2007-11-12T13:32:09Z | Thesis |
163 | 2007-11-12T09:58:28Z | Thesis | DUT-002364
172 | 2007-11-12T13:32:30Z | Thesis |
168 | 2007-11-12T13:32:02Z | Thesis |
166 | 2007-11-12T13:31:43Z | Thesis |
(7 rows)
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
