Repository navigation
Expand file tree
/
Copy pathqueries.sql
More file actions
57 lines (48 loc) · 1.66 KB
/
Copy pathqueries.sql
File metadata and controls
57 lines (48 loc) · 1.66 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- Program.Id => Program[Observation[Step[_]]
SELECT p.program_id,
p.title,
o.observation_id,
o.title,
s.index,
s.instrument,
s.step_type,
sg.gcal_lamp,
sg.shutter,
sc.offset_p,
sc.offset_q
FROM program p
LEFT OUTER JOIN observation o ON o.program_id = p.program_id
LEFT OUTER JOIN step s ON s.observation_id = o.observation_id
LEFT OUTER JOIN step_gcal sg
ON sg.observation_id = s.observation_id AND sg.index = s.index
LEFT OUTER JOIN step_science sc
ON sc.observation_id = s.observation_id AND sc.index = s.index
WHERE p.program_id = 'Benoit'
ORDER BY observation_id, index;
-- Program.Id => Program[Observation[Nothing]] or Program[Observation.Id]
SELECT p.program_id,
p.title,
o.observation_id,
o.title
FROM program p LEFT OUTER JOIN observation o ON o.program_id = p.program_id
WHERE p.program_id = 'Benoit'
ORDER BY observation_id, index;
-- Observation.Id => Observation[Step[_]]
SELECT o.observation_id,
o.title,
s.index,
s.instrument,
s.step_type,
sg.gcal_lamp,
sg.shutter,
sc.offset_p,
sc.offset_q
FROM observation o
LEFT OUTER JOIN step s ON s.observation_id = o.observation_id
LEFT OUTER JOIN step_gcal sg
ON sg.observation_id = s.observation_id AND sg.index = s.index
LEFT OUTER JOIN step_science sc
ON sc.observation_id = s.observation_id AND sc.index = s.index
WHERE o.program_id = 'Benoit' AND o.observation_index = 12
ORDER BY observation_id, index
;