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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
# Linking genotype files to datasets
## Tags
* assigned: alexm
* type: debug
* priority: high
* status: in progress
## Notes
A dataset can have multiple groups. In order to fetch all possible sample data fetch all possible groups in a dataset then use that to fetch all linked genofiles
As an example example the dataset HC_M2_0606_P is of type Probeset and linked groups for this dataset include (bxd,others) that would translate to these genofiles BXD.geno etc
## Species relationship to groups/
A dataset has got species for example (mouse,human)
Similary a species can have multiple groups/families
Example-> Species(mouse) ->Families(BXD family, B6D2RI, B6D2F2) -> Genofiles related to this (BXD.geno, B6D2RI.geno, B6D2F2.geno)
* How to fetch Families given a species
```sql
SELECT InbredSet.Name, InbredSet.FullName
FROM InbredSet, Species WHERE Species.Name = 'mouse'
AND InbredSet.SpeciesId = Species.Id GROUP by
InbredSet.Name ORDER BY IFNULL(InbredSet.FamilyOrder,
InbredSet.FullName) ASC, IFNULL(InbredSet.Family,
InbredSet.FullName) ASC, InbredSet.FullName ASC,
InbredSet.MenuOrderId ASC
```
* Limit you fetch to specified type for Dataset
Example of types Probeset,Publish
```sql
select InbredSet.Name, InbredSet.FullName from InbredSet,Species,
ProbeFreeze where Species.Name = 'mouse'
and InbredSet.SpeciesId = Species.Id and
(ProbeFreeze.InbredSetId = InbredSet.Id)
group by InbredSet.Name
order by InbredSet.FullName
```
## Notes of linking dataset to genofile
```sql
SELECT PublishFreeze.Name, PublishFreeze.FullName
FROM PublishFreeze, InbredSet
WHERE InbredSet.Name = 'BXD' AND
PublishFreeze.InbredSetId = InbredSet.Id
ORDER BY PublishFreeze.CreateTime ASC
```
fetch all publish
```sql
SELECT PublishFreeze.Name, PublishFreeze.FullName
FROM PublishFreeze, InbredSet
WHERE
PublishFreeze.InbredSetId = InbredSet.Id
ORDER BY PublishFreeze.CreateTime ASC
```
#other phenotypes
```sql
SELECT InfoFiles.GN_AccesionId, PublishFreeze.Name,
PublishFreeze.FullName FROM InfoFiles, PublishFreeze,
InbredSet WHERE InbredSet.Name = 'BXD' AND
PublishFreeze.InbredSetId = InbredSet.Id AND
InfoFiles.InfoPageName = PublishFreeze.Name
ORDER BY PublishFreeze.CreateTime ASC
```
fetch all groups
```sql
SELECT InfoFiles.GN_AccesionId, PublishFreeze.Name,
InbredSet.Name FROM InfoFiles, PublishFreeze,
InbredSet WHERE
PublishFreeze.InbredSetId = InbredSet.Id AND
InfoFiles.InfoPageName = PublishFreeze.Name
ORDER BY PublishFreeze.CreateTime ASC
```
```sql
+---------------+------------+------+
| GN_AccesionId | Name | Name |
+---------------+------------+------+
| 602 | BXDPublish | BXD |
| 635 | HLCPublish | HLC |
+---------------+------------+------+
```
** genotypes
Fetch specific group
```sql
SELECT InfoFiles.GN_AccesionId
FROM InfoFiles, GenoFreeze, InbredSet
WHERE InbredSet.Name = 'BXD' AND
GenoFreeze.InbredSetId = InbredSet.Id AND
InfoFiles.InfoPageName = GenoFreeze.ShortName
ORDER BY GenoFreeze.CreateTime
DESC
```
Fetch all groups
SELECT InfoFiles.GN_AccesionId,InbredSet.Name
FROM InfoFiles, GenoFreeze, InbredSet
WHERE
GenoFreeze.InbredSetId = InbredSet.Id AND
InfoFiles.InfoPageName = GenoFreeze.ShortName
ORDER BY GenoFreeze.CreateTime
DESC
results for example related files are
```sql
results
+---------------+------+
| GN_AccesionId | Name |
+---------------+------+
| 600 | BXD |
+---------------+------+
600Geno
```
|
