# 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
```