Contains one row for every unique Dartid value in the ANESTHS table.[267] Each row statistically summarizes the ANESTHS rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.79. Query Defining the ANESTH_STATS View
SELECT anesths.dartid AS dartid
, count(*) AS ansamps
, avg(anesths.anamount) AS anamount_mean
, stddev(anesths.anamount) AS anamount_stddev
FROM anesths
GROUP BY anesths.dartid;
Table 6.37. Columns in the ANESTH_STATS View
Column | From | Description |
---|---|---|
Dartid | ANESTHS.Dartid | Identifier of the darting event. |
Ansamps | Computed | Number of ANESTHS rows having the given Dartid value -- the number of times additional anesthetic was administered during the darting. |
Anamount_mean | ANESTHS.Anamount (computed) | The arithmetic mean of the additional anesthetic amounts related to the given Dartid -- the mean of the additional anesthetic administered during the darting. |
Anamount_stddev | ANESTHS.Anamount (computed) | The standard deviation of the additional anesthetic amounts related to the given Dartid -- the standard deviation of the additional anesthetic administered during the darting. |
Contains one row for every unique Dartid value in the BODYTEMPS table.[268] Each row statistically summarizes the BODYTEMPS rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.81. Query Defining the BODYTEMP_STATS View
SELECT bodytemps.dartid AS dartid
, count(*) AS btsamps
, avg(bodytemps.btemp) AS btemp_mean
, stddev(bodytemps.btemp) AS btemp_stddev
FROM bodytemps
GROUP BY bodytemps.dartid;
Table 6.38. Columns in the BODYTEMP_STATS View
Column | From | Description |
---|---|---|
Dartid | BODYTEMPS.Dartid | Identifier of the darting event. |
Btsamps | Computed | Number of BODYTEMPS rows having the given Dartid value -- the number of body temperature measurements taken during the darting. |
Btemp_mean | BODYTEMPS.Btemp (computed) | The arithmetic mean of the body temperature measurements related to the given Dartid -- the mean of the body temperature measurements taken during the darting. |
Btemp_stddev | BODYTEMPS.Btemp (computed) | The standard deviation of the body temperature measurements related to the given Dartid -- the standard deviation of the body temperature measurements taken during the darting. |
Contains one row for every unique Dartid value in the CHESTS table.[269] Each row statistically summarizes the CHESTS rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.83. Query Defining the CHEST_STATS View
SELECT chests.dartid AS dartid
, count(*) AS chsamps
, avg(chests.chcircum) AS chcircum_mean
, stddev(chests.chcircum) AS chcircum_stddev
, avg(chests.chunadjusted) AS chunadjusted_mean
, stddev(chests.chunadjusted) AS chunadjusted_stddev
FROM chests
GROUP BY chests.dartid;
Table 6.39. Columns in the CHEST_STATS View
Column | From | Description |
---|---|---|
Dartid | CHESTS.Dartid | Identifier of the darting event. |
Chsamps | Computed | Number of CHESTS rows having the given Dartid value -- the number of chest circumference measurements taken during the darting. |
Chcircum_mean | CHESTS.Chcircum (computed) | The arithmetic mean of the chest circumference measurements related to the given Dartid -- the mean of the chest circumference measurements taken during the darting. |
Chcircum_stddev | CHESTS.Chcircum (computed) | The standard deviation of the chest circumference measurements related to the given Dartid -- the standard deviation of the chest circumference measurements taken during the darting. |
Chunadjusted_mean | CHESTS.Chunadjusted (computed) | The arithmetic mean of the unadjusted chest circumference measurements related to the given Dartid -- the mean of the unadjusted chest circumference measurements taken during the darting. |
Chunadjusted_stddev | CHESTS.Chunadjusted (computed) | The standard deviation of the unadjusted chest circumference measurements related to the given Dartid -- the standard deviation of the unadjusted chest circumference measurements taken during the darting. |
Contains one row for every unique Dartid value in the CROWNRUMPS table.[270] Each row statistically summarizes the CROWNRUMPS rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.85. Query Defining the CROWNRUMP_STATS View
SELECT crownrumps.dartid AS dartid
, count(*) AS crsamps
, avg(crownrumps.crlength) AS crlength_mean
, stddev(crownrumps.crlength) AS crlength_stddev
FROM crownrumps
GROUP BY crownrumps.dartid;
Table 6.40. Columns in the CROWNRUMP_STATS View
Column | From | Description |
---|---|---|
Dartid | CROWNRUMPS.Dartid | Identifier of the darting event. |
CRsamps | Computed | Number of CROWNRUMPS rows having the given Dartid value -- the number of crown-to-rump measurements taken during the darting. |
CRlength_mean | CROWNRUMPS.CRlength (computed) | The arithmetic mean of the crown-to-rump measurements related to the given Dartid -- the mean of the crown-to-rump measurements taken during the darting. |
CRlength_stddev | CROWNRUMPS.CRlength (computed) | The standard deviation of the crown-to-rump measurements related to the given Dartid -- the standard deviation of the crown-to-rump measurements taken during the darting. |
Contains one row for every darting.[271] Each row contains columns from DART_SAMPLES for every existing DART_SAMPLES.DS_Type. This shows all samples
collected during the given darting in one row. When there is
no information about how many of a particular DS_Type were collected, the column
indicating that sample type is NULL
.
One column appears in DSAMPLES for each DART_SAMPLE_TYPES.DS_Type.
Figure 6.87. Query Defining the DSAMPLES View
SELECT dartings.dartid
, dartings.sname
, dartings.date
, members.grp
, blood_unspecs.num AS bloodunspec
, blood_paxgenes.num AS bloodpaxgene
, blood_purpletops.num AS bloodpurpletops
, blood_separators.num AS bloodseptube
, blood_cpts.num AS bloodcpt
, blood_trucultures.num AS bloodtruculture
, blood_smears.num AS bloodsmear
, tc_bloods.num AS tcblood
, hair_unspecs.num AS hairunspec
, hair_lengths.num AS hairlength
, hair_cu_zns.num AS haircu_zn
, teeth_3mouths.num AS mouthphotos3
, teeth_lmandmolds.num AS lmandmold
, teeth_lmaxmolds.num AS lmaxillamold
, teeth_lmol1mol2s.num AS lm1m2siliconemold
, skin_punchs.num AS skinpunch
, tc_skins.num AS tcskin
, vag_swabs.num AS vaginalswab
, cerv_swabs.num AS cervicalswab
, fecal_formalin.num AS fecal_formalin
, vaginal_ph.num AS vaginal_ph
, palm_swab.num AS palm_swab
, tongue_swab.num AS tongue_swab
, tooth_plaque_swab.num as tooth_plaque_swab
, vagswab_microbiome.num AS vagswab_microbiome
, glans_penis_swab.num AS glans_penis_swab
, fecal_microbiome.num AS fecal_microbiome
, nostrils_swab.num AS nostrils_swab
, skin_behind_ear_swab.num AS skin_behind_ear_swab
, skin_inside_elbow_swab.num AS skin_inside_elbow_swab
FROM dartings
JOIN members
ON dartings.sname = members.sname
AND dartings.date = members.date
LEFT JOIN dart_samples blood_unspecs
ON dartings.dartid = blood_unspecs.dartid
AND blood_unspecs.ds_type = 1
LEFT JOIN dart_samples blood_paxgenes
ON dartings.dartid = blood_paxgenes.dartid
AND blood_paxgenes.ds_type = 2
LEFT JOIN dart_samples blood_purpletops
ON dartings.dartid = blood_purpletops.dartid
AND blood_purpletops.ds_type = 3
LEFT JOIN dart_samples blood_separators
ON dartings.dartid = blood_separators.dartid
AND blood_separators.ds_type = 4
LEFT JOIN dart_samples blood_cpts
ON dartings.dartid = blood_cpts.dartid
AND blood_cpts.ds_type = 5
LEFT JOIN dart_samples blood_trucultures
ON dartings.dartid = blood_trucultures.dartid
AND blood_trucultures.ds_type = 6
LEFT JOIN dart_samples blood_smears
ON dartings.dartid = blood_smears.dartid
AND blood_smears.ds_type = 7
LEFT JOIN dart_samples hair_unspecs
ON dartings.dartid = hair_unspecs.dartid
AND hair_unspecs.ds_type = 8
LEFT JOIN dart_samples hair_lengths
ON dartings.dartid = hair_lengths.dartid
AND hair_lengths.ds_type = 9
LEFT JOIN dart_samples hair_cu_zns
ON dartings.dartid = hair_cu_zns.dartid
AND hair_cu_zns.ds_type = 10
LEFT JOIN dart_samples teeth_3mouths
ON dartings.dartid = teeth_3mouths.dartid
AND teeth_3mouths.ds_type = 11
LEFT JOIN dart_samples teeth_lmandmolds
ON dartings.dartid = teeth_lmandmolds.dartid
AND teeth_lmandmolds.ds_type = 12
LEFT JOIN dart_samples teeth_lmaxmolds
ON dartings.dartid = teeth_lmaxmolds.dartid
AND teeth_lmaxmolds.ds_type = 13
LEFT JOIN dart_samples teeth_lmol1mol2s
ON dartings.dartid = teeth_lmol1mol2s.dartid
AND teeth_lmol1mol2s.ds_type = 14
LEFT JOIN dart_samples skin_punchs
ON dartings.dartid = skin_punchs.dartid
AND skin_punchs.ds_type = 15
LEFT JOIN dart_samples vag_swabs
ON dartings.dartid = vag_swabs.dartid
AND vag_swabs.ds_type = 16
LEFT JOIN dart_samples cerv_swabs
ON dartings.dartid = cerv_swabs.dartid
AND cerv_swabs.ds_type = 17
LEFT JOIN dart_samples tc_bloods
ON dartings.dartid = tc_bloods.dartid
AND tc_bloods.ds_type = 18
LEFT JOIN dart_samples tc_skins
ON dartings.dartid = tc_skins.dartid
AND tc_skins.ds_type = 19
LEFT JOIN dart_samples fecal_formalin
ON dartings.dartid = fecal_formalin.dartid
AND fecal_formalin.ds_type = 20
LEFT JOIN dart_samples vaginal_ph
ON dartings.dartid = vaginal_ph.dartid
AND vaginal_ph.ds_type = 21
LEFT JOIN dart_samples palm_swab
ON dartings.dartid = palm_swab.dartid
AND palm_swab.ds_type = 22
LEFT JOIN dart_samples tongue_swab
ON dartings.dartid = tongue_swab.dartid
AND tongue_swab.ds_type = 23
LEFT JOIN dart_samples tooth_plaque_swab
ON dartings.dartid = tooth_plaque_swab.dartid
AND tooth_plaque_swab.ds_type = 24
LEFT JOIN dart_samples vagswab_microbiome
ON dartings.dartid = vagswab_microbiome.dartid
AND vagswab_microbiome.ds_type = 25
LEFT JOIN dart_samples glans_penis_swab
ON dartings.dartid = glans_penis_swab.dartid
AND glans_penis_swab.ds_type = 26
LEFT JOIN dart_samples fecal_microbiome
ON dartings.dartid = fecal_microbiome.dartid
AND fecal_microbiome.ds_type = 27
LEFT JOIN dart_samples nostrils_swab
ON dartings.dartid = nostrils_swab.dartid
AND nostrils_swab.ds_type = 28
LEFT JOIN dart_samples skin_behind_ear_swab
ON dartings.dartid = skin_behind_ear_swab.dartid
AND skin_behind_ear_swab.ds_type = 29
LEFT JOIN dart_samples skin_inside_elbow_swab
ON dartings.dartid = skin_inside_elbow_swab.dartid
AND skin_inside_elbow_swab.ds_type = 30;
Because most of the columns in DSAMPLES are based on the rows present in DART_SAMPLE_TYPES there is not a description of
each column here. For columns indicating a number of a
number of collected samples, the column name is always an
abbreviated version of the DS_Type description. For
example, a DART_SAMPLE_TYPES.DS_Type whose DART_SAMPLE_TYPES.Descr is LEFT
MANDIBLE MOLD
will be counted in the
DSAMPLES.Lmandmold column. These columns are described below
in a generic fashion.
Table 6.41. Columns in the DSAMPLES View
Column | From | Description |
---|---|---|
Dartid | DARTINGS.Dartid | Identifier of the darting event. |
Sname | DARTINGS.Sname | The Sname of the darted individual. |
Date | DARTINGS.Date | The date of the darting. |
Grp | MEMBERS.Grp | The study group the individual was in, on the darting date. |
[Sample counts] | DART_SAMPLES.Num | The number of samples collected of the type indicated by the column name. |
Contains one row for every darting during which
dentition information was taken.[272] Each row contains columns from TEETH for every existing TOOTHCODES.Toothcode
value. This shows all the tooth-related information
collecting during the given darting as one row, in a fashion
that is structured based on the teeth found in baboons.
When there is no information on a particular tooth the
values in the columns having to do with that tooth are
NULL
.
Two columns appear in DENT_CODES for every TOOTHCODES.Toothcode
value. A column named TCtstate
, where the
TOOTHCODES.Toothcode
value replaces the letters “TC”, shows the TEETH.Tstate of the tooth.
A column named TCtcondition
, where the
TOOTHCODES.Toothcode
value replaces the letters “TC”, shows the TEETH.Tcondition of the
tooth.
Adding or deleting TOOTHCODES.Toothcode does not automatically change the DENT_CODES view. The view must be manually re-coded to reflect changes made to TOOTHCODES.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.88. Query Defining the DENT_CODES View
SELECT teethdartids.dartid AS dartid
, rum3.rum3tstate AS rum3tstate
, rum3.rum3tcondition AS rum3tcondition
, rum2.rum2tstate AS rum2tstate
, rum2.rum2tcondition AS rum2tcondition
, rum1.rum1tstate AS rum1tstate
, rum1.rum1tcondition AS rum1tcondition
, rup2.rup2tstate AS rup2tstate
, rup2.rup2tcondition AS rup2tcondition
, rup1.rup1tstate AS rup1tstate
, rup1.rup1tcondition AS rup1tcondition
, ruc.ructstate AS ructstate
, ruc.ructcondition AS ructcondition
, rui2.rui2tstate AS rui2tstate
, rui2.rui2tcondition AS rui2tcondition
, rui1.rui1tstate AS rui1tstate
, rui1.rui1tcondition AS rui1tcondition
, lui1.lui1tstate AS lui1tstate
, lui1.lui1tcondition AS lui1tcondition
, lui2.lui2tstate AS lui2tstate
, lui2.lui2tcondition AS lui2tcondition
, luc.luctstate AS luctstate
, luc.luctcondition AS luctcondition
, lup1.lup1tstate AS lup1tstate
, lup1.lup1tcondition AS lup1tcondition
, lup2.lup2tstate AS lup2tstate
, lup2.lup2tcondition AS lup2tcondition
, lum1.lum1tstate AS lum1tstate
, lum1.lum1tcondition AS lum1tcondition
, lum2.lum2tstate AS lum2tstate
, lum2.lum2tcondition AS lum2tcondition
, lum3.lum3tstate AS lum3tstate
, lum3.lum3tcondition AS lum3tcondition
, llm3.llm3tstate AS llm3tstate
, llm3.llm3tcondition AS llm3tcondition
, llm2.llm2tstate AS llm2tstate
, llm2.llm2tcondition AS llm2tcondition
, llm1.llm1tstate AS llm1tstate
, llm1.llm1tcondition AS llm1tcondition
, llp2.llp2tstate AS llp2tstate
, llp2.llp2tcondition AS llp2tcondition
, llp1.llp1tstate AS llp1tstate
, llp1.llp1tcondition AS llp1tcondition
, llc.llctstate AS llctstate
, llc.llctcondition AS llctcondition
, lli2.lli2tstate AS lli2tstate
, lli2.lli2tcondition AS lli2tcondition
, lli1.lli1tstate AS lli1tstate
, lli1.lli1tcondition AS lli1tcondition
, rli1.rli1tstate AS rli1tstate
, rli1.rli1tcondition AS rli1tcondition
, rli2.rli2tstate AS rli2tstate
, rli2.rli2tcondition AS rli2tcondition
, rlc.rlctstate AS rlctstate
, rlc.rlctcondition AS rlctcondition
, rlp1.rlp1tstate AS rlp1tstate
, rlp1.rlp1tcondition AS rlp1tcondition
, rlp2.rlp2tstate AS rlp2tstate
, rlp2.rlp2tcondition AS rlp2tcondition
, rlm1.rlm1tstate AS rlm1tstate
, rlm1.rlm1tcondition AS rlm1tcondition
, rlm2.rlm2tstate AS rlm2tstate
, rlm2.rlm2tcondition AS rlm2tcondition
, rlm3.rlm3tstate AS rlm3tstate
, rlm3.rlm3tcondition AS rlm3tcondition
, drum2.drum2tstate AS drum2tstate
, drum2.drum2tcondition AS drum2tcondition
, drum1.drum1tstate AS drum1tstate
, drum1.drum1tcondition AS drum1tcondition
, druc.dructstate AS dructstate
, druc.dructcondition AS dructcondition
, drui2.drui2tstate AS drui2tstate
, drui2.drui2tcondition AS drui2tcondition
, drui1.drui1tstate AS drui1tstate
, drui1.drui1tcondition AS drui1tcondition
, dlui1.dlui1tstate AS dlui1tstate
, dlui1.dlui1tcondition AS dlui1tcondition
, dlui2.dlui2tstate AS dlui2tstate
, dlui2.dlui2tcondition AS dlui2tcondition
, dluc.dluctstate AS dluctstate
, dluc.dluctcondition AS dluctcondition
, dlum1.dlum1tstate AS dlum1tstate
, dlum1.dlum1tcondition AS dlum1tcondition
, dlum2.dlum2tstate AS dlum2tstate
, dlum2.dlum2tcondition AS dlum2tcondition
, dllm2.dllm2tstate AS dllm2tstate
, dllm2.dllm2tcondition AS dllm2tcondition
, dllm1.dllm1tstate AS dllm1tstate
, dllm1.dllm1tcondition AS dllm1tcondition
, dllc.dllctstate AS dllctstate
, dllc.dllctcondition AS dllctcondition
, dlli2.dlli2tstate AS dlli2tstate
, dlli2.dlli2tcondition AS dlli2tcondition
, dlli1.dlli1tstate AS dlli1tstate
, dlli1.dlli1tcondition AS dlli1tcondition
, drli1.drli1tstate AS drli1tstate
, drli1.drli1tcondition AS drli1tcondition
, drli2.drli2tstate AS drli2tstate
, drli2.drli2tcondition AS drli2tcondition
, drlc.drlctstate AS drlctstate
, drlc.drlctcondition AS drlctcondition
, drlm1.drlm1tstate AS drlm1tstate
, drlm1.drlm1tcondition AS drlm1tcondition
, drlm2.drlm2tstate AS drlm2tstate
, drlm2.drlm2tcondition AS drlm2tcondition
FROM (SELECT teeth.dartid
FROM teeth
GROUP BY teeth.dartid)
AS teethdartids
LEFT OUTER JOIN
(SELECT teeth.dartid AS rum3dartid
, teeth.tstate AS rum3tstate
, teeth.tcondition AS rum3tcondition
FROM teeth
WHERE teeth.tooth = 'rum3')
AS rum3
ON rum3.rum3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rum2dartid
, teeth.tstate AS rum2tstate
, teeth.tcondition AS rum2tcondition
FROM teeth
WHERE teeth.tooth = 'rum2')
AS rum2
ON rum2.rum2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rum1dartid
, teeth.tstate AS rum1tstate
, teeth.tcondition AS rum1tcondition
FROM teeth
WHERE teeth.tooth = 'rum1')
AS rum1
ON rum1.rum1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rup2dartid
, teeth.tstate AS rup2tstate
, teeth.tcondition AS rup2tcondition
FROM teeth
WHERE teeth.tooth = 'rup2')
AS rup2
ON rup2.rup2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rup1dartid
, teeth.tstate AS rup1tstate
, teeth.tcondition AS rup1tcondition
FROM teeth
WHERE teeth.tooth = 'rup1')
AS rup1
ON rup1.rup1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rucdartid
, teeth.tstate AS ructstate
, teeth.tcondition AS ructcondition
FROM teeth
WHERE teeth.tooth = 'ruc')
AS ruc
ON ruc.rucdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rui2dartid
, teeth.tstate AS rui2tstate
, teeth.tcondition AS rui2tcondition
FROM teeth
WHERE teeth.tooth = 'rui2')
AS rui2
ON rui2.rui2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rui1dartid
, teeth.tstate AS rui1tstate
, teeth.tcondition AS rui1tcondition
FROM teeth
WHERE teeth.tooth = 'rui1')
AS rui1
ON rui1.rui1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lui1dartid
, teeth.tstate AS lui1tstate
, teeth.tcondition AS lui1tcondition
FROM teeth
WHERE teeth.tooth = 'lui1')
AS lui1
ON lui1.lui1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lui2dartid
, teeth.tstate AS lui2tstate
, teeth.tcondition AS lui2tcondition
FROM teeth
WHERE teeth.tooth = 'lui2')
AS lui2
ON lui2.lui2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lucdartid
, teeth.tstate AS luctstate
, teeth.tcondition AS luctcondition
FROM teeth
WHERE teeth.tooth = 'luc')
AS luc
ON luc.lucdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lup1dartid
, teeth.tstate AS lup1tstate
, teeth.tcondition AS lup1tcondition
FROM teeth
WHERE teeth.tooth = 'lup1')
AS lup1
ON lup1.lup1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lup2dartid
, teeth.tstate AS lup2tstate
, teeth.tcondition AS lup2tcondition
FROM teeth
WHERE teeth.tooth = 'lup2')
AS lup2
ON lup2.lup2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lum1dartid
, teeth.tstate AS lum1tstate
, teeth.tcondition AS lum1tcondition
FROM teeth
WHERE teeth.tooth = 'lum1')
AS lum1
ON lum1.lum1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lum2dartid
, teeth.tstate AS lum2tstate
, teeth.tcondition AS lum2tcondition
FROM teeth
WHERE teeth.tooth = 'lum2')
AS lum2
ON lum2.lum2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lum3dartid
, teeth.tstate AS lum3tstate
, teeth.tcondition AS lum3tcondition
FROM teeth
WHERE teeth.tooth = 'lum3')
AS lum3
ON lum3.lum3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llm3dartid
, teeth.tstate AS llm3tstate
, teeth.tcondition AS llm3tcondition
FROM teeth
WHERE teeth.tooth = 'llm3')
AS llm3
ON llm3.llm3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llm2dartid
, teeth.tstate AS llm2tstate
, teeth.tcondition AS llm2tcondition
FROM teeth
WHERE teeth.tooth = 'llm2')
AS llm2
ON llm2.llm2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llm1dartid
, teeth.tstate AS llm1tstate
, teeth.tcondition AS llm1tcondition
FROM teeth
WHERE teeth.tooth = 'llm1')
AS llm1
ON llm1.llm1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llp2dartid
, teeth.tstate AS llp2tstate
, teeth.tcondition AS llp2tcondition
FROM teeth
WHERE teeth.tooth = 'llp2')
AS llp2
ON llp2.llp2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llp1dartid
, teeth.tstate AS llp1tstate
, teeth.tcondition AS llp1tcondition
FROM teeth
WHERE teeth.tooth = 'llp1')
AS llp1
ON llp1.llp1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS llcdartid
, teeth.tstate AS llctstate
, teeth.tcondition AS llctcondition
FROM teeth
WHERE teeth.tooth = 'llc')
AS llc
ON llc.llcdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lli2dartid
, teeth.tstate AS lli2tstate
, teeth.tcondition AS lli2tcondition
FROM teeth
WHERE teeth.tooth = 'lli2')
AS lli2
ON lli2.lli2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS lli1dartid
, teeth.tstate AS lli1tstate
, teeth.tcondition AS lli1tcondition
FROM teeth
WHERE teeth.tooth = 'lli1')
AS lli1
ON lli1.lli1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rli1dartid
, teeth.tstate AS rli1tstate
, teeth.tcondition AS rli1tcondition
FROM teeth
WHERE teeth.tooth = 'rli1')
AS rli1
ON rli1.rli1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rli2dartid
, teeth.tstate AS rli2tstate
, teeth.tcondition AS rli2tcondition
FROM teeth
WHERE teeth.tooth = 'rli2')
AS rli2
ON rli2.rli2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlcdartid
, teeth.tstate AS rlctstate
, teeth.tcondition AS rlctcondition
FROM teeth
WHERE teeth.tooth = 'rlc')
AS rlc
ON rlc.rlcdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlp1dartid
, teeth.tstate AS rlp1tstate
, teeth.tcondition AS rlp1tcondition
FROM teeth
WHERE teeth.tooth = 'rlp1')
AS rlp1
ON rlp1.rlp1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlp2dartid
, teeth.tstate AS rlp2tstate
, teeth.tcondition AS rlp2tcondition
FROM teeth
WHERE teeth.tooth = 'rlp2')
AS rlp2
ON rlp2.rlp2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlm1dartid
, teeth.tstate AS rlm1tstate
, teeth.tcondition AS rlm1tcondition
FROM teeth
WHERE teeth.tooth = 'rlm1')
AS rlm1
ON rlm1.rlm1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlm2dartid
, teeth.tstate AS rlm2tstate
, teeth.tcondition AS rlm2tcondition
FROM teeth
WHERE teeth.tooth = 'rlm2')
AS rlm2
ON rlm2.rlm2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS rlm3dartid
, teeth.tstate AS rlm3tstate
, teeth.tcondition AS rlm3tcondition
FROM teeth
WHERE teeth.tooth = 'rlm3')
AS rlm3
ON rlm3.rlm3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drum2dartid
, teeth.tstate AS drum2tstate
, teeth.tcondition AS drum2tcondition
FROM teeth
WHERE teeth.tooth = 'drum2')
AS drum2
ON drum2.drum2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drum1dartid
, teeth.tstate AS drum1tstate
, teeth.tcondition AS drum1tcondition
FROM teeth
WHERE teeth.tooth = 'drum1')
AS drum1
ON drum1.drum1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drucdartid
, teeth.tstate AS dructstate
, teeth.tcondition AS dructcondition
FROM teeth
WHERE teeth.tooth = 'druc')
AS druc
ON druc.drucdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drui2dartid
, teeth.tstate AS drui2tstate
, teeth.tcondition AS drui2tcondition
FROM teeth
WHERE teeth.tooth = 'drui2')
AS drui2
ON drui2.drui2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drui1dartid
, teeth.tstate AS drui1tstate
, teeth.tcondition AS drui1tcondition
FROM teeth
WHERE teeth.tooth = 'drui1')
AS drui1
ON drui1.drui1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlui1dartid
, teeth.tstate AS dlui1tstate
, teeth.tcondition AS dlui1tcondition
FROM teeth
WHERE teeth.tooth = 'dlui1')
AS dlui1
ON dlui1.dlui1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlui2dartid
, teeth.tstate AS dlui2tstate
, teeth.tcondition AS dlui2tcondition
FROM teeth
WHERE teeth.tooth = 'dlui2')
AS dlui2
ON dlui2.dlui2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlucdartid
, teeth.tstate AS dluctstate
, teeth.tcondition AS dluctcondition
FROM teeth
WHERE teeth.tooth = 'dluc')
AS dluc
ON dluc.dlucdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlum1dartid
, teeth.tstate AS dlum1tstate
, teeth.tcondition AS dlum1tcondition
FROM teeth
WHERE teeth.tooth = 'dlum1')
AS dlum1
ON dlum1.dlum1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlum2dartid
, teeth.tstate AS dlum2tstate
, teeth.tcondition AS dlum2tcondition
FROM teeth
WHERE teeth.tooth = 'dlum2')
AS dlum2
ON dlum2.dlum2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dllm2dartid
, teeth.tstate AS dllm2tstate
, teeth.tcondition AS dllm2tcondition
FROM teeth
WHERE teeth.tooth = 'dllm2')
AS dllm2
ON dllm2.dllm2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dllm1dartid
, teeth.tstate AS dllm1tstate
, teeth.tcondition AS dllm1tcondition
FROM teeth
WHERE teeth.tooth = 'dllm1')
AS dllm1
ON dllm1.dllm1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dllcdartid
, teeth.tstate AS dllctstate
, teeth.tcondition AS dllctcondition
FROM teeth
WHERE teeth.tooth = 'dllc')
AS dllc
ON dllc.dllcdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlli2dartid
, teeth.tstate AS dlli2tstate
, teeth.tcondition AS dlli2tcondition
FROM teeth
WHERE teeth.tooth = 'dlli2')
AS dlli2
ON dlli2.dlli2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS dlli1dartid
, teeth.tstate AS dlli1tstate
, teeth.tcondition AS dlli1tcondition
FROM teeth
WHERE teeth.tooth = 'dlli1')
AS dlli1
ON dlli1.dlli1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drli1dartid
, teeth.tstate AS drli1tstate
, teeth.tcondition AS drli1tcondition
FROM teeth
WHERE teeth.tooth = 'drli1')
AS drli1
ON drli1.drli1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drli2dartid
, teeth.tstate AS drli2tstate
, teeth.tcondition AS drli2tcondition
FROM teeth
WHERE teeth.tooth = 'drli2')
AS drli2
ON drli2.drli2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drlcdartid
, teeth.tstate AS drlctstate
, teeth.tcondition AS drlctcondition
FROM teeth
WHERE teeth.tooth = 'drlc')
AS drlc
ON drlc.drlcdartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drlm1dartid
, teeth.tstate AS drlm1tstate
, teeth.tcondition AS drlm1tcondition
FROM teeth
WHERE teeth.tooth = 'drlm1')
AS drlm1
ON drlm1.drlm1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS drlm2dartid
, teeth.tstate AS drlm2tstate
, teeth.tcondition AS drlm2tcondition
FROM teeth
WHERE teeth.tooth = 'drlm2')
AS drlm2
ON drlm2.drlm2dartid = teethdartids.dartid;
Because the columns in DENT_CODES are based on the rows present in TOOTHCODES there is not a description of each column here. Instead, the columns based on TOOTHCODES.Toothcode are described below in a generic fashion. Each such column is prefaced here with “TC”, which is replaced by a TOOTHCODES.Toothcode value in the actual column name.
Contains one row for every darting during which
dentition information was taken.[273] Each row contains columns from TEETH and TOOTHCODES tables
for every existing TOOTHCODES.Toothsite value. This shows all the
tooth-related information collecting during the given
darting as one row, in a fashion that is structured around
the position of the teeth within the mouth. When there is
no information on a particular tooth the values in the
columns having to do with that tooth are NULL
.
Three columns appear in DENT_SITES for every TOOTHCODES.Toothsite
value. A column named TStstate
, where the
letter s
followed by the TOOTHCODES.Toothsite
value replaces the letters “TS”[274], shows the TEETH.Tstate of the tooth. A column named
TStcondition
, where the letter
s
followed by the TOOTHCODES.Toothsite
value replaces the letters “TS”, shows the TEETH.Tcondition of the
tooth. And a column named TSdeciduous
,
where the letter s
followed by the TOOTHCODES.Toothsite
value replaces the letters “TS”, shows the TOOTHCODES.Deciduous
value for the tooth.
Adding or deleting TOOTHCODES.Toothcode, or changing the TOOTHCODES.Toothsite does not automatically change the DENT_SITES view. The view must be manually re-coded to reflect changes made to TOOTHCODES.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.90. Query Defining the DENT_SITES View
SELECT teethdartids.dartid AS dartid
, s1.s1tstate AS s1tstate
, s1.s1tcondition AS s1tcondition
, s1.s1deciduous AS s1deciduous
, s2.s2tstate AS s2tstate
, s2.s2tcondition AS s2tcondition
, s2.s2deciduous AS s2deciduous
, s3.s3tstate AS s3tstate
, s3.s3tcondition AS s3tcondition
, s3.s3deciduous AS s3deciduous
, s4.s4tstate AS s4tstate
, s4.s4tcondition AS s4tcondition
, s4.s4deciduous AS s4deciduous
, s5.s5tstate AS s5tstate
, s5.s5tcondition AS s5tcondition
, s5.s5deciduous AS s5deciduous
, s6.s6tstate AS s6tstate
, s6.s6tcondition AS s6tcondition
, s6.s6deciduous AS s6deciduous
, s7.s7tstate AS s7tstate
, s7.s7tcondition AS s7tcondition
, s7.s7deciduous AS s7deciduous
, s8.s8tstate AS s8tstate
, s8.s8tcondition AS s8tcondition
, s8.s8deciduous AS s8deciduous
, s9.s9tstate AS s9tstate
, s9.s9tcondition AS s9tcondition
, s9.s9deciduous AS s9deciduous
, s10.s10tstate AS s10tstate
, s10.s10tcondition AS s10tcondition
, s10.s10deciduous AS s10deciduous
, s11.s11tstate AS s11tstate
, s11.s11tcondition AS s11tcondition
, s11.s11deciduous AS s11deciduous
, s12.s12tstate AS s12tstate
, s12.s12tcondition AS s12tcondition
, s12.s12deciduous AS s12deciduous
, s13.s13tstate AS s13tstate
, s13.s13tcondition AS s13tcondition
, s13.s13deciduous AS s13deciduous
, s14.s14tstate AS s14tstate
, s14.s14tcondition AS s14tcondition
, s14.s14deciduous AS s14deciduous
, s15.s15tstate AS s15tstate
, s15.s15tcondition AS s15tcondition
, s15.s15deciduous AS s15deciduous
, s16.s16tstate AS s16tstate
, s16.s16tcondition AS s16tcondition
, s16.s16deciduous AS s16deciduous
, s17.s17tstate AS s17tstate
, s17.s17tcondition AS s17tcondition
, s17.s17deciduous AS s17deciduous
, s18.s18tstate AS s18tstate
, s18.s18tcondition AS s18tcondition
, s18.s18deciduous AS s18deciduous
, s19.s19tstate AS s19tstate
, s19.s19tcondition AS s19tcondition
, s19.s19deciduous AS s19deciduous
, s20.s20tstate AS s20tstate
, s20.s20tcondition AS s20tcondition
, s20.s20deciduous AS s20deciduous
, s21.s21tstate AS s21tstate
, s21.s21tcondition AS s21tcondition
, s21.s21deciduous AS s21deciduous
, s22.s22tstate AS s22tstate
, s22.s22tcondition AS s22tcondition
, s22.s22deciduous AS s22deciduous
, s23.s23tstate AS s23tstate
, s23.s23tcondition AS s23tcondition
, s23.s23deciduous AS s23deciduous
, s24.s24tstate AS s24tstate
, s24.s24tcondition AS s24tcondition
, s24.s24deciduous AS s24deciduous
, s25.s25tstate AS s25tstate
, s25.s25tcondition AS s25tcondition
, s25.s25deciduous AS s25deciduous
, s26.s26tstate AS s26tstate
, s26.s26tcondition AS s26tcondition
, s26.s26deciduous AS s26deciduous
, s27.s27tstate AS s27tstate
, s27.s27tcondition AS s27tcondition
, s27.s27deciduous AS s27deciduous
, s28.s28tstate AS s28tstate
, s28.s28tcondition AS s28tcondition
, s28.s28deciduous AS s28deciduous
, s29.s29tstate AS s29tstate
, s29.s29tcondition AS s29tcondition
, s29.s29deciduous AS s29deciduous
, s30.s30tstate AS s30tstate
, s30.s30tcondition AS s30tcondition
, s30.s30deciduous AS s30deciduous
, s31.s31tstate AS s31tstate
, s31.s31tcondition AS s31tcondition
, s31.s31deciduous AS s31deciduous
, s32.s32tstate AS s32tstate
, s32.s32tcondition AS s32tcondition
, s32.s32deciduous AS s32deciduous
FROM (SELECT teeth.dartid
FROM teeth
GROUP BY teeth.dartid)
AS teethdartids
LEFT OUTER JOIN
(SELECT teeth.dartid AS s1dartid
, teeth.tstate AS s1tstate
, teeth.tcondition AS s1tcondition
, toothcodes.deciduous AS s1deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '1'
AND teeth.tooth = toothcodes.tooth)
AS s1
ON s1.s1dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s2dartid
, teeth.tstate AS s2tstate
, teeth.tcondition AS s2tcondition
, toothcodes.deciduous AS s2deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '2'
AND teeth.tooth = toothcodes.tooth)
AS s2
ON s2.s2dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s3dartid
, teeth.tstate AS s3tstate
, teeth.tcondition AS s3tcondition
, toothcodes.deciduous AS s3deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '3'
AND teeth.tooth = toothcodes.tooth)
AS s3
ON s3.s3dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s4dartid
, teeth.tstate AS s4tstate
, teeth.tcondition AS s4tcondition
, toothcodes.deciduous AS s4deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '4'
AND teeth.tooth = toothcodes.tooth)
AS s4
ON s4.s4dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s5dartid
, teeth.tstate AS s5tstate
, teeth.tcondition AS s5tcondition
, toothcodes.deciduous AS s5deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '5'
AND teeth.tooth = toothcodes.tooth)
AS s5
ON s5.s5dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s6dartid
, teeth.tstate AS s6tstate
, teeth.tcondition AS s6tcondition
, toothcodes.deciduous AS s6deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '6'
AND teeth.tooth = toothcodes.tooth)
AS s6
ON s6.s6dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s7dartid
, teeth.tstate AS s7tstate
, teeth.tcondition AS s7tcondition
, toothcodes.deciduous AS s7deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '7'
AND teeth.tooth = toothcodes.tooth)
AS s7
ON s7.s7dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s8dartid
, teeth.tstate AS s8tstate
, teeth.tcondition AS s8tcondition
, toothcodes.deciduous AS s8deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '8'
AND teeth.tooth = toothcodes.tooth)
AS s8
ON s8.s8dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s9dartid
, teeth.tstate AS s9tstate
, teeth.tcondition AS s9tcondition
, toothcodes.deciduous AS s9deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '9'
AND teeth.tooth = toothcodes.tooth)
AS s9
ON s9.s9dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s10dartid
, teeth.tstate AS s10tstate
, teeth.tcondition AS s10tcondition
, toothcodes.deciduous AS s10deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '10'
AND teeth.tooth = toothcodes.tooth)
AS s10
ON s10.s10dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s11dartid
, teeth.tstate AS s11tstate
, teeth.tcondition AS s11tcondition
, toothcodes.deciduous AS s11deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '11'
AND teeth.tooth = toothcodes.tooth)
AS s11
ON s11.s11dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s12dartid
, teeth.tstate AS s12tstate
, teeth.tcondition AS s12tcondition
, toothcodes.deciduous AS s12deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '12'
AND teeth.tooth = toothcodes.tooth)
AS s12
ON s12.s12dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s13dartid
, teeth.tstate AS s13tstate
, teeth.tcondition AS s13tcondition
, toothcodes.deciduous AS s13deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '13'
AND teeth.tooth = toothcodes.tooth)
AS s13
ON s13.s13dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s14dartid
, teeth.tstate AS s14tstate
, teeth.tcondition AS s14tcondition
, toothcodes.deciduous AS s14deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '14'
AND teeth.tooth = toothcodes.tooth)
AS s14
ON s14.s14dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s15dartid
, teeth.tstate AS s15tstate
, teeth.tcondition AS s15tcondition
, toothcodes.deciduous AS s15deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '15'
AND teeth.tooth = toothcodes.tooth)
AS s15
ON s15.s15dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s16dartid
, teeth.tstate AS s16tstate
, teeth.tcondition AS s16tcondition
, toothcodes.deciduous AS s16deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '16'
AND teeth.tooth = toothcodes.tooth)
AS s16
ON s16.s16dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s17dartid
, teeth.tstate AS s17tstate
, teeth.tcondition AS s17tcondition
, toothcodes.deciduous AS s17deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '17'
AND teeth.tooth = toothcodes.tooth)
AS s17
ON s17.s17dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s18dartid
, teeth.tstate AS s18tstate
, teeth.tcondition AS s18tcondition
, toothcodes.deciduous AS s18deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '18'
AND teeth.tooth = toothcodes.tooth)
AS s18
ON s18.s18dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s19dartid
, teeth.tstate AS s19tstate
, teeth.tcondition AS s19tcondition
, toothcodes.deciduous AS s19deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '19'
AND teeth.tooth = toothcodes.tooth)
AS s19
ON s19.s19dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s20dartid
, teeth.tstate AS s20tstate
, teeth.tcondition AS s20tcondition
, toothcodes.deciduous AS s20deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '20'
AND teeth.tooth = toothcodes.tooth)
AS s20
ON s20.s20dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s21dartid
, teeth.tstate AS s21tstate
, teeth.tcondition AS s21tcondition
, toothcodes.deciduous AS s21deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '21'
AND teeth.tooth = toothcodes.tooth)
AS s21
ON s21.s21dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s22dartid
, teeth.tstate AS s22tstate
, teeth.tcondition AS s22tcondition
, toothcodes.deciduous AS s22deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '22'
AND teeth.tooth = toothcodes.tooth)
AS s22
ON s22.s22dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s23dartid
, teeth.tstate AS s23tstate
, teeth.tcondition AS s23tcondition
, toothcodes.deciduous AS s23deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '23'
AND teeth.tooth = toothcodes.tooth)
AS s23
ON s23.s23dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s24dartid
, teeth.tstate AS s24tstate
, teeth.tcondition AS s24tcondition
, toothcodes.deciduous AS s24deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '24'
AND teeth.tooth = toothcodes.tooth)
AS s24
ON s24.s24dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s25dartid
, teeth.tstate AS s25tstate
, teeth.tcondition AS s25tcondition
, toothcodes.deciduous AS s25deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '25'
AND teeth.tooth = toothcodes.tooth)
AS s25
ON s25.s25dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s26dartid
, teeth.tstate AS s26tstate
, teeth.tcondition AS s26tcondition
, toothcodes.deciduous AS s26deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '26'
AND teeth.tooth = toothcodes.tooth)
AS s26
ON s26.s26dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s27dartid
, teeth.tstate AS s27tstate
, teeth.tcondition AS s27tcondition
, toothcodes.deciduous AS s27deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '27'
AND teeth.tooth = toothcodes.tooth)
AS s27
ON s27.s27dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s28dartid
, teeth.tstate AS s28tstate
, teeth.tcondition AS s28tcondition
, toothcodes.deciduous AS s28deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '28'
AND teeth.tooth = toothcodes.tooth)
AS s28
ON s28.s28dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s29dartid
, teeth.tstate AS s29tstate
, teeth.tcondition AS s29tcondition
, toothcodes.deciduous AS s29deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '29'
AND teeth.tooth = toothcodes.tooth)
AS s29
ON s29.s29dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s30dartid
, teeth.tstate AS s30tstate
, teeth.tcondition AS s30tcondition
, toothcodes.deciduous AS s30deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '30'
AND teeth.tooth = toothcodes.tooth)
AS s30
ON s30.s30dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s31dartid
, teeth.tstate AS s31tstate
, teeth.tcondition AS s31tcondition
, toothcodes.deciduous AS s31deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '31'
AND teeth.tooth = toothcodes.tooth)
AS s31
ON s31.s31dartid = teethdartids.dartid
LEFT OUTER JOIN
(SELECT teeth.dartid AS s32dartid
, teeth.tstate AS s32tstate
, teeth.tcondition AS s32tcondition
, toothcodes.deciduous AS s32deciduous
FROM toothcodes, teeth
WHERE toothcodes.toothsite = '32'
AND teeth.tooth = toothcodes.tooth)
AS s32
ON s32.s32dartid = teethdartids.dartid;
Because the columns in DENT_SITES
are based on the rows present in TOOTHCODES there is not a description of each
column here. Instead, the columns based on TOOTHCODES.Toothsite are described below in a
generic fashion. Each such column is prefaced here with
“TS”, which is replaced by the letter
s
followed by a TOOTHCODES.Toothsite value in the actual column
name.
Table 6.43. Columns in the DENT_SITES View
Column | From | Description |
---|---|---|
Dartid | TEETH.Dartid | Identifier of the darting event. |
TStstate | TEETH.Tstate | Code indicating the degree to which the tooth
exists. When NULL no information on the tooth was
recorded during the darting. |
TStcondition | TEETH.Tcondition | Code indicating the condition of the tooth. |
TSdeciduous | TOOTHCODES.Deciduous | True when the tooth is
deciduous, False when it is
not. |
Contains one row for every unique Dartid value in the HUMERUSES table.[275] Each row statistically summarizes the HUMERUSES rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.92. Query Defining the HUMERUS_STATS View
SELECT humeruses.dartid AS dartid
, count(*) AS husamps
, avg(humeruses.hulength) AS hulength_mean
, stddev(humeruses.hulength) AS hulength_stddev
, avg(humeruses.huunadjusted) AS huunadjusted_mean
, stddev(humeruses.huunadjusted) AS huunadjusted_stddev
FROM humeruses
GROUP BY humeruses.dartid;
Table 6.44. Columns in the HUMERUS_STATS View
Column | From | Description |
---|---|---|
Dartid | HUMERUSES.Dartid | Identifier of the darting event. |
Husamps | Computed | Number of HUMERUSES rows having the given Dartid value -- the number of humerus length measurements taken during the darting. |
Hulength_mean | HUMERUSES.Hulength (computed) | The arithmetic mean of the humerus length measurements related to the given Dartid -- the mean of the humerus length measurements taken during the darting. |
Hulength_stddev | HUMERUSES.Hulength (computed) | The standard deviation of the humerus length measurements related to the given Dartid -- the standard deviation of the humerus length measurements taken during the darting. |
Huunadjusted_mean | HUMERUSES.Huunadjusted (computed) | The arithmetic mean of the unadjusted humerus length measurements related to the given Dartid -- the mean of the unadjusted humerus length measurements taken during the darting. |
Huunadjusted_stddev | HUMERUSES.Huunadjusted (computed) | The standard deviation of the unadjusted humerus length measurements related to the given Dartid -- the standard deviation of the unadjusted humerus length measurements taken during the darting. |
Contains one row for every unique Dartid value in the PCVS table.[276] Each row statistically summarizes the PCVS rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.94. Query Defining the PCV_STATS View
SELECT pcvs.dartid AS dartid
, count(*) AS pcvsamps
, avg(pcvs.pcv) AS pcv_mean
, stddev(pcvs.pcv) AS pcv_stddev
FROM pcvs
GROUP BY pcvs.dartid;
Table 6.45. Columns in the PCV_STATS View
Column | From | Description |
---|---|---|
Dartid | PCVS.Dartid | Identifier of the darting event. |
PCVsamps | Computed | Number of PCVS rows having the given Dartid value -- the number of PCV measurements taken during the darting. |
PCV_mean | PCVS.PCV (computed) | The arithmetic mean of the PCV measurements related to the given Dartid -- the mean of the PCV measurements taken during the darting. |
PCV_stddev | PCVS.PCV (computed) | The standard deviation of the PCV measurements related to the given Dartid -- the standard deviation of the PCV measurements taken during the darting. |
Contains one row for every unique Dartid value in the TESTES_ARC table.[277] Each row statistically summarizes the TESTES_ARC rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.96. Query Defining the TESTES_ARC_STATS View
SELECT testesdartids.dartid AS dartid
, testesllength.testllengthsamps AS testllengthsamps
, testesllength.testllength_mean AS testllength_mean
, testesllength.testllength_stddev AS testllength_stddev
, testeslwidth.testlwidthsamps AS testlwidthsamps
, testeslwidth.testlwidth_mean AS testlwidth_mean
, testeslwidth.testlwidth_stddev AS testlwidth_stddev
, testesrlength.testrlengthsamps AS testrlengthsamps
, testesrlength.testrlength_mean AS testrlength_mean
, testesrlength.testrlength_stddev AS testrlength_stddev
, testesrwidth.testrwidthsamps AS testrwidthsamps
, testesrwidth.testrwidth_mean AS testrwidth_mean
, testesrwidth.testrwidth_stddev AS testrwidth_stddev
FROM (SELECT testes_arc.dartid
FROM testes_arc
GROUP BY testes_arc.dartid)
AS testesdartids
LEFT OUTER JOIN
(SELECT testes_arc.dartid AS llengthdartid
, count(*) AS testllengthsamps
, avg(testes_arc.testlength) AS testllength_mean
, stddev(testes_arc.testlength) AS testllength_stddev
FROM testes_arc
WHERE testes_arc.testside = 'L'
AND testes_arc.testlength IS NOT NULL
GROUP BY testes_arc.dartid)
AS testesllength
ON testesllength.llengthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_arc.dartid AS lwidthdartid
, count(*) AS testlwidthsamps
, avg(testes_arc.testwidth) AS testlwidth_mean
, stddev(testes_arc.testwidth) AS testlwidth_stddev
FROM testes_arc
WHERE testes_arc.testside = 'L'
AND testes_arc.testwidth IS NOT NULL
GROUP BY testes_arc.dartid)
AS testeslwidth
ON testeslwidth.lwidthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_arc.dartid AS rlengthdartid
, count(*) AS testrlengthsamps
, avg(testes_arc.testlength) AS testrlength_mean
, stddev(testes_arc.testlength) AS testrlength_stddev
FROM testes_arc
WHERE testes_arc.testside = 'R'
AND testes_arc.testlength IS NOT NULL
GROUP BY testes_arc.dartid)
AS testesrlength
ON testesrlength.rlengthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_arc.dartid AS rwidthdartid
, count(*) AS testrwidthsamps
, avg(testes_arc.testwidth) AS testrwidth_mean
, stddev(testes_arc.testwidth) AS testrwidth_stddev
FROM testes_arc
WHERE testes_arc.testside = 'R'
AND testes_arc.testwidth IS NOT NULL
GROUP BY testes_arc.dartid)
AS testesrwidth
ON testesrwidth.rwidthdartid = testesdartids.dartid;
Table 6.46. Columns in the TESTES_ARC_STATS View
Column | From | Description |
---|---|---|
Dartid | TESTES_ARC.Dartid (computed) | Identifier of the darting event. |
Testllengthsamps | Computed | Number of TESTES_ARC rows having the
given Dartid value and also having a TESTES_ARC.Testside value
of L and a non-NULL
TESTES_ARC.Testlength
value -- the number of left testicle length
measurements taken during the darting.[a] |
Testllength_mean | TESTES_ARC.Testlength (computed) | The arithmetic mean of the left testicle length measurements related to the given Dartid -- the mean of the left testicle length measurements taken during the darting. |
Testllength_stddev | TESTES_ARC.Testlength (computed) | The standard deviation of the left testicle length measurements related to the given Dartid -- the standard deviation of the left testicle length measurements taken during the darting. |
Testlwidthsamps | Computed | Number of TESTES_ARC rows having the
given Dartid value and also having a TESTES_ARC.Testside value
of L and a non-NULL
TESTES_ARC.Testwidth value
-- the number of left testicle width measurements
taken during the darting.[b] |
Testlwidth_mean | TESTES_ARC.Testwidth (computed) | The arithmetic mean of the left testicle width measurements related to the given Dartid -- the mean of the left testicle width measurements taken during the darting. |
Testlwidth_stddev | TESTES_ARC.Testwidth (computed) | The standard deviation of the left testicle width measurements related to the given Dartid -- the standard deviation of the left testicle width measurements taken during the darting. |
Testrlengthsamps | Computed | Number of TESTES_ARC rows having the
given Dartid value and also having a TESTES_ARC.Testside value
of R and a
non-NULL TESTES_ARC.Testlength
value -- the number of right testicle length
measurements taken during the darting.[c] |
Testrlength_mean | TESTES_ARC.Testlength (computed) | The arithmetic mean of the right testicle length measurements related to the given Dartid -- the mean of the right testicle length measurements taken during the darting. |
Testrlength_stddev | TESTES_ARC.Testlength (computed) | The standard deviation of the right testicle length measurements related to the given Dartid -- the standard deviation of the right testicle length measurements taken during the darting. |
Testrwidthsamps | Computed | Number of TESTES_ARC rows having the
given Dartid value and also having a TESTES_ARC.Testside value
of R and a
non-NULL TESTES_ARC.Testwidth value
-- the number of right testicle width measurements
taken during the darting.[d] |
Testrwidth_mean | TESTES_ARC.Testwidth (computed) | The arithmetic mean of the right testicle width measurements related to the given Dartid -- the mean of the right testicle width measurements taken during the darting. |
Testrwidth_stddev | TESTES_ARC.Testwidth (computed) | The standard deviation of the right testicle width measurements related to the given Dartid -- the standard deviation of the right testicle width measurements taken during the darting. |
[a] [b] [c] [d] |
Contains one row for every unique Dartid value in the TESTES_DIAM table.[278] Each row statistically summarizes the TESTES_DIAM rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.98. Query Defining the TESTES_DIAM_STATS View
SELECT testesdartids.dartid AS dartid
, testesllength.testllengthsamps AS testllengthsamps
, testesllength.testllength_mean AS testllength_mean
, testesllength.testllength_stddev AS testllength_stddev
, testeslwidth.testlwidthsamps AS testlwidthsamps
, testeslwidth.testlwidth_mean AS testlwidth_mean
, testeslwidth.testlwidth_stddev AS testlwidth_stddev
, testesrlength.testrlengthsamps AS testrlengthsamps
, testesrlength.testrlength_mean AS testrlength_mean
, testesrlength.testrlength_stddev AS testrlength_stddev
, testesrwidth.testrwidthsamps AS testrwidthsamps
, testesrwidth.testrwidth_mean AS testrwidth_mean
, testesrwidth.testrwidth_stddev AS testrwidth_stddev
FROM (SELECT testes_diam.dartid
FROM testes_diam
GROUP BY testes_diam.dartid)
AS testesdartids
LEFT OUTER JOIN
(SELECT testes_diam.dartid AS llengthdartid
, count(*) AS testllengthsamps
, avg(testes_diam.testlength) AS testllength_mean
, stddev(testes_diam.testlength) AS testllength_stddev
FROM testes_diam
WHERE testes_diam.testside = 'L'
AND testes_diam.testlength IS NOT NULL
GROUP BY testes_diam.dartid)
AS testesllength
ON testesllength.llengthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_diam.dartid AS lwidthdartid
, count(*) AS testlwidthsamps
, avg(testes_diam.testwidth) AS testlwidth_mean
, stddev(testes_diam.testwidth) AS testlwidth_stddev
FROM testes_diam
WHERE testes_diam.testside = 'L'
AND testes_diam.testwidth IS NOT NULL
GROUP BY testes_diam.dartid)
AS testeslwidth
ON testeslwidth.lwidthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_diam.dartid AS rlengthdartid
, count(*) AS testrlengthsamps
, avg(testes_diam.testlength) AS testrlength_mean
, stddev(testes_diam.testlength) AS testrlength_stddev
FROM testes_diam
WHERE testes_diam.testside = 'R'
AND testes_diam.testlength IS NOT NULL
GROUP BY testes_diam.dartid)
AS testesrlength
ON testesrlength.rlengthdartid = testesdartids.dartid
LEFT OUTER JOIN
(SELECT testes_diam.dartid AS rwidthdartid
, count(*) AS testrwidthsamps
, avg(testes_diam.testwidth) AS testrwidth_mean
, stddev(testes_diam.testwidth) AS testrwidth_stddev
FROM testes_diam
WHERE testes_diam.testside = 'R'
AND testes_diam.testwidth IS NOT NULL
GROUP BY testes_diam.dartid)
AS testesrwidth
ON testesrwidth.rwidthdartid = testesdartids.dartid;
Table 6.47. Columns in the TESTES_DIAM_STATS View
Column | From | Description |
---|---|---|
Dartid | TESTES_DIAM.Dartid (computed) | Identifier of the darting event. |
Testllengthsamps | Computed | Number of TESTES_DIAM rows having the
given Dartid value and also having a TESTES_DIAM.Testside value
of L and a non-NULL
TESTES_DIAM.Testlength
value -- the number of left testicle length
measurements taken during the darting.[a] |
Testllength_mean | TESTES_DIAM.Testlength (computed) | The arithmetic mean of the left testicle length measurements related to the given Dartid -- the mean of the left testicle length measurements taken during the darting. |
Testllength_stddev | TESTES_DIAM.Testlength (computed) | The standard deviation of the left testicle length measurements related to the given Dartid -- the standard deviation of the left testicle length measurements taken during the darting. |
Testlwidthsamps | Computed | Number of TESTES_DIAM rows having the
given Dartid value and also having a TESTES_DIAM.Testside value
of L and a non-NULL
TESTES_DIAM.Testwidth value
-- the number of left testicle width measurements
taken during the darting.[b] |
Testlwidth_mean | TESTES_DIAM.Testwidth (computed) | The arithmetic mean of the left testicle width measurements related to the given Dartid -- the mean of the left testicle width measurements taken during the darting. |
Testlwidth_stddev | TESTES_DIAM.Testwidth (computed) | The standard deviation of the left testicle width measurements related to the given Dartid -- the standard deviation of the left testicle width measurements taken during the darting. |
Testrlengthsamps | Computed | Number of TESTES_DIAM rows having the
given Dartid value and also having a TESTES_DIAM.Testside value
of R and a
non-NULL TESTES_DIAM.Testlength
value -- the number of right testicle length
measurements taken during the darting.[c] |
Testrlength_mean | TESTES_DIAM.Testlength (computed) | The arithmetic mean of the right testicle length measurements related to the given Dartid -- the mean of the right testicle length measurements taken during the darting. |
Testrlength_stddev | TESTES_DIAM.Testlength (computed) | The standard deviation of the right testicle length measurements related to the given Dartid -- the standard deviation of the right testicle length measurements taken during the darting. |
Testrwidthsamps | Computed | Number of TESTES_DIAM rows having the
given Dartid value and also having a TESTES_DIAM.Testside value
of R and a
non-NULL TESTES_DIAM.Testwidth value
-- the number of right testicle width measurements
taken during the darting.[d] |
Testrwidth_mean | TESTES_DIAM.Testwidth (computed) | The arithmetic mean of the right testicle width measurements related to the given Dartid -- the mean of the right testicle width measurements taken during the darting. |
Testrwidth_stddev | TESTES_DIAM.Testwidth (computed) | The standard deviation of the right testicle width measurements related to the given Dartid -- the standard deviation of the right testicle width measurements taken during the darting. |
[a] [b] [c] [d] |
Contains one row for every unique Dartid value in the ULNAS table.[279] Each row statistically summarizes the ULNAS rows having the common Dartid value.
This view is useful when joined with the DARTINGS table on Dartid.
Figure 6.100. Query Defining the ULNA_STATS View
SELECT ulnas.dartid AS dartid
, count(*) AS ulsamps
, avg(ulnas.ullength) AS ullength_mean
, stddev(ulnas.ullength) AS ullength_stddev
, avg(ulnas.ulunadjusted) AS ulunadjusted_mean
, stddev(ulnas.ulunadjusted) AS ulunadjusted_stddev
FROM ulnas
GROUP BY ulnas.dartid;
Table 6.48. Columns in the ULNA_STATS View
Column | From | Description |
---|---|---|
Dartid | ULNAS.Dartid | Identifier of the darting event. |
Ulsamps | Computed | Number of ULNAS rows having the given Dartid value -- the number of ulna length measurements taken during the darting. |
Ullength_mean | ULNAS.Ullength (computed) | The arithmetic mean of the ulna length measurements related to the given Dartid -- the mean of the ulna length measurements taken during the darting. |
Ullength_stddev | ULNAS.Ullength (computed) | The standard deviation of the ulna length measurements related to the given Dartid -- the standard deviation of the ulna length measurements taken during the darting. |
Ulunadjusted_mean | ULNAS.Ulunadjusted (computed) | The arithmetic mean of the unadjusted ulna length measurements related to the given Dartid -- the mean of the unadjusted ulna length measurements taken during the darting. |
Ulunadjusted_stddev | ULNAS.Ulunadjusted (computed) | The standard deviation of the unadjusted ulna length measurements related to the given Dartid -- the standard deviation of the unadjusted ulna length measurements taken during the darting. |
[267] Note that when is no additional anesthetic administered there are no related ANESTHS rows and hence no row in this view for the given darting.
[268] Note that when no body temperature measurements are taken there are no related BODYTEMPS rows and hence no row in this view for the given darting.
[269] Note that when no chest circumference measurements are taken there are no related CHESTS rows and hence no row in this view for the given darting.
[270] Note that when no crown-to-rump measurements are taken there are no related CROWNRUMPS rows and hence no row in this view for the given darting.
[271] Note that even when no collected samples are recorded for a particular darting in DART_SAMPLES, there will still be a row for that darting in this view.
[272] Note that when no tooth observations are recorded there are no related TEETH rows and hence no row in this view for the given darting.
[273] Note that when no tooth observations are recorded there are no related TEETH rows and hence no row in this view for the given darting.
[274] Column names cannot begin with a digit so the letter
s
, for “site”, is used to
preface the name of each column.
[275] Note that when no humerus length measurements are taken there are no related HUMERUSES rows and hence no row in this view for the given darting.
[276] Note that when no PCV measurements are taken there are no related PCVS rows and hence no row in this view for the given darting.
[277] Note that when no testes length or width measurements are taken there are no related TESTES_ARC rows and hence no row in this view for the given darting.
[278] Note that when no testes length or width measurements are taken there are no related TESTES_DIAM rows and hence no row in this view for the given darting.