Need to create a junction table or query separate tables?

[seasonedweb]

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:

455111	4	4	5
455112	5	4	5

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!

[seasonedweb · Accepted solution]

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