Jump to content
TNG Community
fabio

REPORT for BRANCH & FAMILY

Recommended Posts

fabio

Hi, it is my first time here, even if I work on TNG since many years.

I started recently to write SQL reports on my ancestry DB.

Now I ask for an help.

Now I am trying to obtain a query including all people in the same branch as follows:

SELECT bl.branch, p.personID, p.suffix, p.sex, p.lastname, p.firstname, p.birthplace, p.birthdate,
f.husband, f.wife, f.marrdate,
p.deathplace, p.deathdate
FROM tng_branchlinks AS bl
INNER JOIN tng_branches AS b ON (bl.branch=b.branch)
INNER JOIN tng_people AS p ON (bl.persfamID=p.personID)
INNER JOIN tng_families AS f ON(p.personID=f.husband OR p.personID=f.wife)
WHERE bl.branch='T1t001'
ORDER BY p.birthdatetr DESC, p.lastname;
 
the result is:
# Branch Person ID Suffix sex Last Name First Name Birth Place Birth Date husband wife Marriage Date Death Place Death Date
1 T1t001  I2335  1692-1768  Arduini  Francesco  Caprino Veronese - Rubiana tng_search_small.gif  27 Apr 1692  I2335  I2403  16 Feb 1713  Caprino Veronese - Rubiana tng_search_small.gif  17 Feb 1768 
2 T1t001  I2335  1692-1768  Arduini  Francesco  Caprino Veronese - Rubiana tng_search_small.gif  27 Apr 1692  I2335  I2448  1742  Caprino Veronese - Rubiana tng_search_small.gif  17 Feb 1768 
3 T1t001  I2489  1670-1732  S.C.  Madalena °°Arduini Ventura  Caprino Veronese tng_search_small.gif  1670  I2420  I2489  1691  Caprino Veronese - Rubiana tng_search_small.gif  5 Mar 1732 
4 T1t001  I2420  ?1662-1742  Arduini  Ventura  Caprino Veronese - Rubiana tng_search_small.gif  1662  I2420  I2489  1691  Caprino Veronese - Rubiana tng_search_small.gif  7 Oct 1742 
you see husband & wife columns...(in red), I would like to put instead of the code (I2235 etc) the right name:

SQL: SELECT p.personID, p.suffix, p.sex, bl.branch, p.lastname, p.firstname, p.birthplace, p.birthdate, f.marrplace, f.marrdate, p2.branch AS CB, p2.lastname AS CLN, p2.firstname AS CFN, p.deathplace, p.deathdate, f.husband, f.wife FROM tng_branchlinks AS bl INNER JOIN tng_branches AS b ON (bl.branch=b.branch) INNER JOIN tng_people AS p ON (bl.persfamID=p.personID) INNER JOIN tng_families AS f ON (p.personID=f.husband) INNER JOIN tng_people AS p2 ON (p2.personID=f.wife) WHERE bl.branch='T1t001' ORDER BY p.birthdatetr DESC, p.lastname

# Person ID Suffix sex Branch Last Name First Name Birth Place Birth Date Marriage Place Marriage Date CB CLN CFN Death Place Death Date husband wife
1 I2335  1692-1768  T1t001  Arduini  Francesco  Caprino Veronese - Rubiana tng_search_small.gif  27 Apr 1692  Caprino Veronese - chiesa S.Maria Maggiore tng_search_small.gif  16 Feb 1713  T1,T1t002  Dalle Vedove  Angela  Caprino Veronese - Rubiana tng_search_small.gif  17 Feb 1768  I2335  I2403 
2 I2335  1692-1768  T1t001  Arduini  Francesco  Caprino Veronese - Rubiana tng_search_small.gif  27 Apr 1692  Caprino Veronese - chiesa S.Maria Maggiore [?] tng_search_small.gif  1742    S.C.  Angela °°Ardoini F.  Caprino Veronese - Rubiana tng_search_small.gif  17 Feb 1768  I2335  I2448 
3 I2420  ?1662-1742  T1t001  Arduini  Ventura  Caprino Veronese - Rubiana tng_search_small.gif  1662  Caprino Veronese - chiesa S.Maria Maggiore [?] tng_search_small.gif  1691  T1,T1t001  S.C.  Madalena °°Arduini Ventura  Caprino Veronese - Rubiana tng_search_small.gif  7 Oct 1742  I2420  I2489 
 
So the result is OK but only for Males !
 
The previous #3 record I2489 does not appear.
I tried to do an UNION statement joinig a couple of  Queris but no result.
 
CAN ANYBODY help me !
Thanks
Fabio
 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×