[Babase] male dispersal query

Niki Learn nlearn at princeton.edu
Thu May 20 16:49:35 EDT 2010


Hmm...I don't see how that would have helped since there were 487 births,
all with unique pids...  I had to cross-reference pregs with mtd_cycles then
reference that again with biograph for both mom and infant and then add on
the resumes in Excel so I could calculate the intervals I needed (since I
can't add one to a pid to get the cycling duration for the next cycle after
a birth in repstats either...).

These are my queries:
1)select mtd_cycles.sname, mtd_cycles.ddate, pregs.pid, pregs.resume from
mtd_cycles, pregs where mtd_cycles.dcpid = pregs.conceive and
mtd_cycles.ddate > '1999-12-31' order by mtd_cycles.sname;  which I then
uploaded as conceptions (except later we changed over to births and then I
had to add in some that had been conceived in 1999 but born in 2000)
2)select biograph.sname, conceptions.kidpid, biograph.birth,
biograph.statdate, biograph.status from conceptions, biograph where
conceptions.momsname = biograph.sname;
3)select biograph.pid, biograph.sname, biograph.matgrp, biograph.birth,
biograph.sex, biograph.statdate, biograph.status from conceptions, biograph
where conceptions.kidpid = biograph.pid;
4)select mtd_cycles.sname, conceptions.kidpid, conceptions.resume,
mtd_cycles.tdate from conceptions, mtd_cycles where conceptions.resume =
mtd_cycles.tcpid order by conceptions.kidpid;

With output (lined up in Excel by pid) where each row contained (with values
for ABB1 as an example):

Mom_sname = ABB
Kid_pid = ABB1
Conceive (ddate) = 20 Sep 2004
Mom_birth = 10 Sep 1998
Momn_age at conception (yrs) = 6.03
Mom_statdate = 8 Feb 2008
Mom_status = 1
Kid_sname = ABD
Kid_matgrp = 1.21
Kid_birth = 15 Mar 2005
Kid_sex = M
Kid_status = 1
Kid_age (days) = 1310
Next resume (tdate) = 17 Oct 2005
Days_birth_to_resume (calculated in Excel or queried with repstats) = 216
Days_resume_to_conception (calculated in Excel by subtracting resume date
from the conceive date for the next pid on the next row where the next pid
is - I could not get babase to tell me this from any table or combination of
tables except on a case by case basis, which would be insane to do for 487
births, minus whichever moms died somewhere in the middle) = 278 (as the
conceive date for ABB2 = 22 July 2006)

The results were very interesting though.


The way to "put them together" is to use a self-join.
That's where the same table shows up twice in a single
query.  So you can take 2 queries and "merge" them
into one.

http://databases.about.com/od/sql/a/selfjoin.htm


Karl <kop at meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein


_______________________________________________
Babase mailing list
Babase at www.eco.princeton.edu
http://www.eco.princeton.edu/mailman/listinfo/babase



More information about the Babase mailing list