Project

General

Profile

Download (3.17 KB) Statistics
| Revision:

root/inputs/VegBank/plantconcept_/create.sql @ 4521

1 4521 aaronmk 
SELECT
2 
*
3 
, (SELECT plantname.plantname
4 
    FROM plantconcept_ancestors(plantconcept_id)
5 
    JOIN plantconcept USING (plantconcept_id)
6 
    JOIN plantname USING (plantname_id)
7 
    WHERE plantlevel = 'Kingdom'
8 
    LIMIT 1
9 
    ) AS "Kingdom"
10 
, (SELECT plantname.plantname
11 
    FROM plantconcept_ancestors(plantconcept_id)
12 
    JOIN plantconcept USING (plantconcept_id)
13 
    JOIN plantname USING (plantname_id)
14 
    WHERE plantlevel = 'Subkingdom'
15 
    LIMIT 1
16 
    ) AS "Subkingdom"
17 
, (SELECT plantname.plantname
18 
    FROM plantconcept_ancestors(plantconcept_id)
19 
    JOIN plantconcept USING (plantconcept_id)
20 
    JOIN plantname USING (plantname_id)
21 
    WHERE plantlevel = 'Superdivision'
22 
    LIMIT 1
23 
    ) AS "Superdivision"
24 
, (SELECT plantname.plantname
25 
    FROM plantconcept_ancestors(plantconcept_id)
26 
    JOIN plantconcept USING (plantconcept_id)
27 
    JOIN plantname USING (plantname_id)
28 
    WHERE plantlevel = 'Division'
29 
    LIMIT 1
30 
    ) AS "Division"
31 
, (SELECT plantname.plantname
32 
    FROM plantconcept_ancestors(plantconcept_id)
33 
    JOIN plantconcept USING (plantconcept_id)
34 
    JOIN plantname USING (plantname_id)
35 
    WHERE plantlevel = 'Subdivision'
36 
    LIMIT 1
37 
    ) AS "Subdivision"
38 
, (SELECT plantname.plantname
39 
    FROM plantconcept_ancestors(plantconcept_id)
40 
    JOIN plantconcept USING (plantconcept_id)
41 
    JOIN plantname USING (plantname_id)
42 
    WHERE plantlevel = 'Class'
43 
    LIMIT 1
44 
    ) AS "Class"
45 
, (SELECT plantname.plantname
46 
    FROM plantconcept_ancestors(plantconcept_id)
47 
    JOIN plantconcept USING (plantconcept_id)
48 
    JOIN plantname USING (plantname_id)
49 
    WHERE plantlevel = 'Subclass'
50 
    LIMIT 1
51 
    ) AS "Subclass"
52 
, (SELECT plantname.plantname
53 
    FROM plantconcept_ancestors(plantconcept_id)
54 
    JOIN plantconcept USING (plantconcept_id)
55 
    JOIN plantname USING (plantname_id)
56 
    WHERE plantlevel = 'Order'
57 
    LIMIT 1
58 
    ) AS "Order"
59 
, (SELECT plantname.plantname
60 
    FROM plantconcept_ancestors(plantconcept_id)
61 
    JOIN plantconcept USING (plantconcept_id)
62 
    JOIN plantname USING (plantname_id)
63 
    WHERE plantlevel = 'Family'
64 
    LIMIT 1
65 
    ) AS "Family"
66 
, (SELECT plantname.plantname
67 
    FROM plantconcept_ancestors(plantconcept_id)
68 
    JOIN plantconcept USING (plantconcept_id)
69 
    JOIN plantname USING (plantname_id)
70 
    WHERE plantlevel = 'Genus'
71 
    LIMIT 1
72 
    ) AS "Genus"
73 
, (SELECT plantname.plantname
74 
    FROM plantconcept_ancestors(plantconcept_id)
75 
    JOIN plantconcept USING (plantconcept_id)
76 
    JOIN plantname USING (plantname_id)
77 
    WHERE plantlevel = 'Species'
78 
    LIMIT 1
79 
    ) AS "Species"
80 
, (SELECT plantname.plantname
81 
    FROM plantconcept_ancestors(plantconcept_id)
82 
    JOIN plantconcept USING (plantconcept_id)
83 
    JOIN plantname USING (plantname_id)
84 
    WHERE plantlevel = 'Subspecies'
85 
    LIMIT 1
86 
    ) AS "Subspecies"
87 
, (SELECT plantname.plantname
88 
    FROM plantconcept_ancestors(plantconcept_id)
89 
    JOIN plantconcept USING (plantconcept_id)
90 
    JOIN plantname USING (plantname_id)
91 
    WHERE plantlevel = 'Variety'
92 
    LIMIT 1
93 
    ) AS "Variety"
94 
, (SELECT plantname.plantname
95 
    FROM plantconcept_ancestors(plantconcept_id)
96 
    JOIN plantconcept USING (plantconcept_id)
97 
    JOIN plantname USING (plantname_id)
98 
    WHERE plantlevel = 'Forma'
99 
    LIMIT 1
100 
    ) AS "Forma"
101 
FROM plantconcept