Finne personer som giftet seg på et sted...
User Rating: 
/ 1
- Details
-
Category: Usermenu
-
Published on Wednesday, 06 February 2013 20:00
-
Written by Ove
-
Hits: 1137
I dag skal vi se på hvordan vi lager en meny i personlisten og som finner personer som har giftet seg på et sted vi angir som parameter. Vi vil gjøre det litt annerledes enn vanlig for å forstå bedre hvordan menysystemet virker. Vi klikker knappen helt til høyre for SQL tekstboksen i personlisten "..." (hvis du har den aller siste versjonen som ble lastet opp 23-02-2013 må du velge Edit/SQL Queries i hovedvinduet) og så går vi helt til bunnen og lager en ny linje der vi skriver "Giftet seg i Dolstad Kirke" som Name og som Category skriver vi "MenuLocations" og som SQL setning skriver vi
WHERE pid IN(SELECT maleid FROM ata_families WHERE marriageplace = 'Dolstad Kirke')
så går vi bare til en annen linje for at den linjen vi lagde skal bli lagret og så lukker vi Queries og åpner Personlisten så skulle vi ha en ny meny på User Menu/Locations som heter "Giftet seg i Dolstad Kirke".
Når man så klikker GO får man listet opp alle menn som har giftet seg i Dolstad Kirke men vi kan enkelt forandre det til å vise alle kvinner bare med å forandre maleid til femaleid i spørringen slik at den blir:
WHERE pid IN(SELECT femaleid FROM ata_families WHERE marriageplace = 'Dolstad Kirke')
Dette er ment å være et eksempel på hvordan man finner personer i personlisten som har giftet seg et bestemt sted og i praksis vil man vel ikke benytte personlisten men heller familelisten og der blir det mye enklere SQL setning som vil bli så enkel som:
Where marriageplace = 'Dolstad Kirke'
Men vi fortsetter i personlisten og nå vil vi finne personer som har giftet seg i regionen "Nordland" og vi skriver:
WHERE pid IN(SELECT maleid FROM ata_families WHERE marriageplace IN(SELECT Name from ata_locations where region = 'Nordland'))
Også her bytter vi bare ut maleid med femaleid for å finne kvinner. Fordi jeg kun har en region som heter Nordland i min database behøver jeg ikke å angi hvilket land det dreier seg om men hvis jeg hadde hatt en region i et annet land enn Norge med region Nordland og jeg kun var interessert i regionen Nordland i Norge så måtte jeg skrive:
WHERE pid IN(SELECT maleid FROM ata_families WHERE marriageplace IN(SELECT Name from ata_locations where country = 'Norway' AND region = 'Nordland'))
Man kan også bytte ut "=" med "Like" for å søke på wildcards som f.eks Like '%Nord%'.
Disse spørringene må kjøres fra personlisten som er slik konstruert at bare den siste delen av den komplette SQL spørringen er tilgjengelig for redigering. (WHERE delen). Det er mulig jeg kommer til å forandre dette slik at man kan skrive komplette SQL spørringer også for personlisten. Men la oss gå til Advanced SQL vinduet og skrive en komplett spørring som vil returnere alle ekteskap og sortert på sted for bryllupet. Den blir som for eksempel:
SELECT fid, femaleid AS IDF, femalename AS FEMALE, malename AS MALE, maleid AS IDM, MarriagePlace AS 'Place of marriage' FROM ata_persons JOIN ata_families ON(femaleid = pid) ORDER BY marriageplace ASC, pid
AS FEMALE betyr at teksten som vises over kolonnen blir rett og slett FEMALE man kan endre det til hva man vil... men bruker man flere ord må man sette ' ' rundt f.eks 'NAME OF WOMAN'.
Til slutt (for denne gang) vil jeg vise hvordan man kan leke litt med SQL spørringer i Advanced SQL vinduet. La oss skrive:
SELECT firstname from ata_persons where pid in
(SELECT pid from ata_persons where fatherid in
(SELECT pid from ata_persons where fatherid in
(SELECT pid from ata_persons where fatherid =
(SELECT fatherid FROM ata_persons where pid = '1'))));
Ikke spør hva dette gjør men "where pid = 1" vil returnere min far og resultatet av spørringen er fornavnet til min sønn.
Her er noen flere eksempler:
(pid = 1 er min far og pid = 2 er min mor)
*********************************************************
select firstname from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1'))));
*********************************************************
Returnere alle barn av mine søskens og mine barn (hvis jeg var kvinne) hvor mine søsken er kvinner
Thomas
Daniel
Anette
Mathias
Mia
Leah
*********************************************************
*************************************************************************
select firstname from ata_persons where motherid in
(select pid from ata_persons where fatherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1')));
*************************************************************************
Returnerer alle barn av mine søsken fordi jeg kun har kvinner som søsken
Trond
Heidi
Tone
Guro
Silje
Marthe
Andreas
*************************************************************************
********************************************************
select firstname from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1')));
********************************************************
Returnere alle barn av mine tanters barn
Liv
Rune
Hanne
********************************************************
***********************************************************************
select firstname from ata_persons where fatherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1'))));
***********************************************************************
Returnere alle barn av mine søskens barn hvor mine søskens barn er menn
Ida-Marie
Sandra
Andrea
***********************************************************************
******************************************************
select firstname from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1'))));
******************************************************
Returnerer alle barn av mine tanters barn hvor mine tanters barn er kvinner
Håvard
******************************************************
******************************************************************
select firstname from ata_persons where fatherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1'))));
******************************************************************
Returnerer alle barn av mine tanters barn hvor mine tanters barn er menn
Mariell
******************************************************************
Man kan også bruke UNION for å slå sammen to utvalg i samme spørring:
For eks:
***********************************************
select firstname from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '2')))) UNION
select firstname from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1'))))
***********************************************
Returnerer barn av nieser på begge sider
Thomas
Daniel
Anette
Håvard
Vendelin
Kasper
Mikael
Mathias
Mia
Leah
***********************************************
Man skulle kanskje tro at man kunne istedet for å bruke UNION bare si where pid = 2 OR pid = 1, men dette gir ikke samme resultat og jeg har ikke i farten noen god forklaring på hvorfor ikke dette går.
For å finne barn av både nieser og nivøer + kusiner 2 ganger forskjøvet på mor og far-side blir det da:
select firstname from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '2')))) union
select firstname from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1')))) union
select firstname from ata_persons where fatherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '2')))) union
select firstname from ata_persons where fatherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where motherid in
(select pid from ata_persons where fatherid =
(select fatherid from ata_persons where pid = '1'))))
***********************************************
Som returnerer i min base:
Thomas
Ida-Marie
Sandra
Daniel
Anette
Håvard
Elisabeth
Vendelin
Kasper
Mikael
Mariell
Mathias
Andrea
Mia
Leah
***********************************************
Vel, ikke alle vil bli funnet, man må nok dobble antall unioner et par ganger til og veksle på Where motherID/fatherID for at absolutt alle personer skal bli funnet, men det er likevel interessant og morsomt å teste ut denne muligheten i SQL. AgetoAgeSqlite bruker ikke denne teknikken for å finne etterkommere men bruker også SQL spørringer men på en mer oversiktelig måte Les mer her. Dette er ment som en ekstra mulighet og som kan være en grei avkobling når man har tid å utforske litt. Hvis noen finner en bedre måte å skrive slike SQL spørringer på håper jeg på tilbakemelding.
Her er et til eksempel som returnerer 74 personer fra min database:
********************************************************************
select pid, firstname || ' ' || lastname as Name from ata_persons where fatherid in (
select PID from ata_persons where fatherid > '-1' and fatherid in
(select fatherid from ata_persons where fatherid > '-1' and pid in
(select fatherid from ata_persons where pid = '1')) union
select PID from ata_persons where fatherid > '-1' and fatherid in
(select fatherid from ata_persons where fatherid > '-1' and pid in
(select motherid from ata_persons where pid = '1'))) union
select pid, firstname || ' ' || lastname as Name from ata_persons where motherid in (
select PID from ata_persons where fatherid > '-1' and fatherid in
(select fatherid from ata_persons where fatherid > '-1' and pid in
(select fatherid from ata_persons where pid = '1')) union
select PID from ata_persons where fatherid > '-1' and fatherid in
(select fatherid from ata_persons where fatherid > '-1' and pid in
(select motherid from ata_persons where pid = '1')))
union
select pid, firstname || ' ' || lastname as Name from ata_persons where fatherid in (
select PID from ata_persons where fatherid > '-1' and fatherid in
(select fatherid from ata_persons where fatherid > '-1' and pid in
(select fatherid from ata_persons where pid = '2')) union
select PID from ata_persons where fatherid > '-1' and fatherid in
(select fatherid from ata_persons where fatherid > '-1' and pid in
(select motherid from ata_persons where pid = '2'))) union
select pid, firstname || ' ' || lastname as Name from ata_persons where motherid in (
select PID from ata_persons where fatherid > '-1' and fatherid in
(select fatherid from ata_persons where fatherid > '-1' and pid in
(select fatherid from ata_persons where pid = '2')) union
select PID from ata_persons where fatherid > '-1' and fatherid in
(select fatherid from ata_persons where fatherid > '-1' and pid in
(select motherid from ata_persons where pid = '2')))
*********************************************************************************
Her tester jeg for en fatherid eller motherid med verdi -1, denne verdien er for personer som ikke har registrert far eller mor og i såfall er de ikke aktuelle i denne spørringen.
En ny oppdatering hvor dette siste eksemplet er inkludert og som finnes på Advanced SQL's User menu/Example of finding relatives er lastet opp. (12-februar-2013).
16-februar-2013 - Lekingen med SQL setninger førte til at jeg fant en fin måte å finne alle oldebarn på og utvidet Personskjemaet slik at man kan lage spørringer også her som finnes på fanen "User defined". Ved opprettelse av ny database blir denne funksjonen automatisk lagt til men man kan godt legge den til manuelt i eksisterende databaser og her er spørringen for oldebarn man vil skrive:
select pid, firstname || ' ' || lastname as Name, Birthplace, Birthdate from ata_persons where fatherid in (
select pid from ata_persons where fatherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid))
union
select pid, firstname || ' ' || lastname as Name, Birthplace, Birthdate from ata_persons where fatherid in (
select pid from ata_persons where motherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid))
union
select pid, firstname || ' ' || lastname as Name, Birthplace, Birthdate from ata_persons where motherid in (
select pid from ata_persons where fatherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid))
union
select pid, firstname || ' ' || lastname as Name, birthplace, birthdate from ata_persons where motherid in (
select pid from ata_persons where motherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid))
Man kan selvsagt selv bestemme hvilke felt man vil vise.....her har jeg valgt å slå sammen fornavn og etternavn til Name og så har jeg lagt inn Birthplace og Birthdate men dette er helt opp til brukeren å bestemme. Det eneste som kreves er at det første feltet skal være PID. Funksjonen skal jo fungere med alle personer som subjekt i personskjemeat så derfor må det brukes et parameter @pid som programmet finner verdi for ved kjøring og derfor må man alltid bruke @pid, et lite enkelt eksempel....man ønsker å vise birthdate for personen som er subjekt på personskjemaet...og skriver en spørring for dette, den vil da se slik ut:
Select PID, Birthdate from ata_persons where PID = @pid
Når programmet kjører vil @pid bli byttet ut med verdien for PID for subject på personskjemaet. Men husk at dette med @pid gjelder bare for spørringer som kjøres fra personskjemaet. Man kan ikke kjøre en slik spørring fra f.eks. personlisten. (men ikke farlig å gjøre det om man absolutt vil prøve).
VIKTIG! Man må sette Category i Queries vinduet til "PShemaStandard" eller i hvertfall må den begynne med "PShema" for at funksjonen skal bli tilgjengelig på personskjemaet, og man må selvsagt skrive et navn på spørringen. f.eks "Oldebarn".
Man kan lage og lagre flere spørringer med å klikke "..." på høyresiden av nedtrekkslisten over brukerfunksjoner og bare å velge "Save as new...", men husk å forandre navn for spørringen først.
Dette systemet åpner for at brukere kan utvikle sine egne SQL spørringer og dele med andre brukere om dem vil. Også andre tabeller enn person tabellen kan inkluderes i spørringen, slik at det faktisk er mulig å skrive en spørring for personskjemaet som også vil vise kolonner som hører til andre tabeller f.eks tabellen over "steder" kan også inkluderes. Et lite eksempel:
select pid, firstname, lastname, Country, Region from ata_persons join ata_locations on birthplace = Name and pid = @pid
Dette vil vise 2 kolonner (Country og Region) på personskjemaet som slett ikke hører til person tabellen.
Okay god slektsforskning!
*************************************************
19-februar-2013 - Jeg måtte selvfølgelig også lage en SQL setning som returnerer alle oldebarnebarn også og den var virkelig artig å ha som "User defined" funksjon på personskjemaet og her er den:
select PID, firstname || ' ' || lastname as Name, Birthplace, Birthdate from ata_persons where fatherid in (
select pid from ata_persons where fatherid in(
select pid from ata_persons where fatherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid)))
union
select pid, firstname || ' ' || lastname as Name, birthplace, birthdate from ata_persons where fatherid in (
select pid from ata_persons where motherid in(
select pid from ata_persons where fatherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid)))
union
select pid, firstname || ' ' || lastname as Name, birthplace, birthdate from ata_persons where motherid in (
select pid from ata_persons where fatherid in(
select pid from ata_persons where fatherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid)))
union
select pid, firstname || ' ' || lastname as Name, birthplace, birthdate from ata_persons where motherid in (
select pid from ata_persons where motherid in(
select pid from ata_persons where fatherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid)))
union
select pid, firstname || ' ' || lastname as Name, birthplace, birthdate from ata_persons where fatherid in (
select pid from ata_persons where fatherid in(
select pid from ata_persons where motherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid)))
union
select pid, firstname || ' ' || lastname as Name, birthplace, birthdate from ata_persons where fatherid in (
select pid from ata_persons where motherid in(
select pid from ata_persons where motherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid)))
union
select pid, firstname || ' ' || lastname as Name, birthplace, birthdate from ata_persons where motherid in (
select pid from ata_persons where fatherid in(
select pid from ata_persons where motherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid)))
union
select pid, firstname || ' ' || lastname as Name, birthplace, birthdate from ata_persons where motherid in (
select pid from ata_persons where motherid in(
select pid from ata_persons where motherid in(
select pid from ata_persons where fatherid = @pid or motherid = @pid)))
