-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdatasetbyid.sql
More file actions
13 lines (13 loc) · 892 Bytes
/
datasetbyid.sql
File metadata and controls
13 lines (13 loc) · 892 Bytes
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE FUNCTION da.datasetbyid(_dataid integer)
RETURNS TABLE( datasetid integer, datasettype character varying)
LANGUAGE sql
AS $function$
SELECT ndb.datasets.datasetid, ndb.datasettypes.datasettype
FROM ndb.chronologies INNER JOIN ndb.collectionunits ON ndb.chronologies.collectionunitid = ndb.collectionunits.collectionunitid INNER JOIN
ndb.datasets ON ndb.collectionunits.collectionunitid = ndb.datasets.collectionunitid INNER JOIN
ndb.datasettypes ON ndb.datasets.datasettypeid = ndb.datasettypes.datasettypeid INNER JOIN
ndb.sampleages ON ndb.chronologies.chronologyid = ndb.sampleages.chronologyid INNER JOIN
ndb.samples ON ndb.sampleages.sampleid = ndb.samples.sampleid AND ndb.datasets.datasetid = ndb.samples.datasetid
WHERE ndb.sampleages.chronologyid = _dataid
GROUP BY ndb.datasets.datasetid, ndb.datasettypes.datasettype;
$function$