From 2c22e593c59a9b4f9129a2e669443709d9c5154a Mon Sep 17 00:00:00 2001
From: BonfaceKilz
Date: Wed, 9 Feb 2022 16:52:00 +0300
Subject: doc: Document how to fetch case-attribute data from a sample-list

---
 doc/database.org | 21 +++++++++++++++++++++
 1 file changed, 21 insertions(+)

(limited to 'doc/database.org')

diff --git a/doc/database.org b/doc/database.org
index d5462d4e..99d2905a 100644
--- a/doc/database.org
+++ b/doc/database.org
@@ -1370,6 +1370,27 @@ Empty set (0.00 sec)
 
 Hmmm. This is the test database. Then there are the plink files and VCF files.
 
+** How to fetch case attribute data
+
+To fetch case attribute data, you need the PublixshXRef Id and it's
+PhenotypeId.  Run the following query (as an example):
+
+#+begin_src sql
+SELECT concat(st.Name, ',', ifnull(pd.value, 'x'), ',',
+       ifnull(ps.error, 'x'), ',', ifnull(ns.count, 'x')) as 'Data',
+       ifnull(ca.Name, 'x') as 'CaseAttr', ifnull(cxref.value, 'x') as 'Value'
+FROM PublishFreeze pf
+JOIN PublishXRef px ON px.InbredSetId = pf.InbredSetId
+JOIN PublishData pd ON pd.Id = px.DataId
+JOIN Strain st ON pd.StrainId = st.Id
+LEFT JOIN PublishSE ps ON ps.DataId = pd.Id AND ps.StrainId = pd.StrainId
+LEFT JOIN NStrain ns ON ns.DataId = pd.Id AND ns.StrainId = pd.StrainId
+LEFT JOIN CaseAttributeXRefNew cxref ON
+           cxref.InbredSetId = px.InbredSetId
+LEFT JOIN CaseAttribute ca ON ca.Id = cxref.CaseAttributeId
+WHERE px.Id = 10006 AND px.PhenotypeId = 28409 LIMIT 10;
+#+end_src
+
 * Optimize SQL?
 
 We were facing some issues with slow queries. A query
-- 
cgit v1.2.3