Welcome Dialog

Welcome to the Community!

We have a brand new look! Take a tour with us and explore the latest updates on Adobe Support Community.


Need to create a junction table or query separate tables?

Explorer ,
Jan 03, 2021 Jan 03, 2021

Copy link to clipboard

Copied

Greetings,

This correct method is most likely staring me in the face, but my head hurts after 2 days of SQL headaches.

Attempting to import GEDCOM (.ged) formated data to SQL Server is not easy.

GEDCOM is supposed to be a universal data format for genealogical data (Ancestry etc. etc.).

In order to get this data into a SQL DB, one needs to import .ged to .csv to .mdb & finally to SQL. GEDCOM columns, relationships and schema are suppsed to be standardized- not yet, by a long shot. My goal is a results query that matches spouses using the tables as they were contructed in GEDCOM.

The "FamilyTable" contains FartherID & MotherID columns.

The "NameTable" contains NameID , Surname, Given.

The NameID = everyone in the DB

Probably an easy answer to query both tables.

Since there are 4000 records (people) in the DB, the output I'm looking for would be table rows (2000) with Father-surname-given, Mother-surname-given.

I thought a junction table would solve issues easily, but when I created it, it has double entries.

Each record has a PK named "couple_ID" then 4000 entries like so:

455111445
455112545

 

IOW one row showing father to mother, next row mother to father.

Using a corrected junction table,

<cfquery name="list_spouses" datasource="#Request.BaseDSN3#">
SELECT spouse_name_ID, spouse_male_ID, spouse_female_ID, Surname, Given
FROM NameTable, junction_spouses
WHERE ??
</cfquery>

Or query of 2 existing  tables:

<cfquery name="get_couples" datasource="#Request.BaseDSN3#">

SELECT NameID, FatherID, MotherID, Surname, Given
FROM NameTable, FamilyTable
WHERE ?
</cfquery>

Left joins? Query of query? Thank you for looking!

 

 

 

 

 

 

 

Views

80

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 03, 2021 Jan 03, 2021

Copy link to clipboard

Copied

LATEST

existing GEDCOM tables (unfortunately, "spouseID imported all null values):

name_table.jpgperson_table.jpg

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines