• assigned: alexm
• type: debug
• priority: high
• status: in progress

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

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

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

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 

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

SELECT PublishFreeze.Name, PublishFreeze.FullName 
FROM PublishFreeze, InbredSet
WHERE
PublishFreeze.InbredSetId = InbredSet.Id 
ORDER BY PublishFreeze.CreateTime ASC

#other phenotypes

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

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

+---------------+------------+------+
| GN_AccesionId | Name       | Name |
+---------------+------------+------+
|           602 | BXDPublish | BXD  |
|           635 | HLCPublish | HLC  |
+---------------+------------+------+

** genotypes

Fetch specific group

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

results 

+---------------+------+
| GN_AccesionId | Name |
+---------------+------+
|           600 | BXD  |
+---------------+------+



600Geno