Skip to main content
S
seasonedweb
Inspiring
December 11, 2021
Answered

Matching Married Couples?

  • December 11, 2021
  • 1 reply
  • 559 views

Hello

Each person in this DB has their unique ID (ged_ID), and a spouse ID (ged_spouse_ID).

Also ged_lastname, ged_firstname

I'm having trouble with the SQL query structure to output:

Person First, Last, - Spouse First, Last names on a single row.

Would this be set up as a single query, qoqs, or SQL Joins "WHERE IN" etc.?

Sorry if this is staring me in face- probably is...

Norman B.

This topic has been closed for replies.
Correct answer Charlie Arehart

Thanks Charlie- this works great!

SELECT a.ged_firstname, a.ged_lastname, '-', b.ged_firstname as spouse_firstname, b.ged_lastname as spouse_lastname
FROM ged_main AS a
LEFT JOIN ged_main AS b
ON a.ged_spouse_id=b.ged_id
</cfquery>

 

<tr class="trdynamic">
<td class="tdheaderdynamic">Last Name</td>
<td class="tdheaderdynamic">First Name</td>
<td class="tdheaderdynamic">Spouse Last Name</td>
<td class="tdheaderdynamic">Spouse First Name</td>

</tr><cfoutput>
<cfloop query="match_spouse">

<tr>

<td class="tddynamic">#ged_firstname#</td>

<td class="tddynamic">#ged_lastname# </td>

<td class="tddynamic">#spouse_firstname#</td>

<td class="tddynamic">#spouse_lastname#</td>

 

</tr>
</cfloop>
</cfoutput>

</table>

 

How would I prevent the double entries

Marie Cleophas OlivineRoyJoseph HyacintheBoisvert
Joseph HyacintheBoisvertMarie Cleophas OlivineRoy

 

?

?

 


Try adding after the join:

 

And a.ged_spouse_id<>b.ged_spouse_id

 

Again, this is just a guess. A less obvious choice in such a self-join is to change the <> to a <. Test both carefully, and let us k own how it goes. 

1 reply

Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
December 11, 2021

My guess is to do a join yes, on the table itself. I assume that the value of ged_spouse_id would be equal to their spouse's ged_id. And since I assume not everyone in the table would have a spouse, you don't want to do a traditional (equi) join. Instead, you could do an outer join, such as a left join so:

 

Select a.ged_firstname, a.ged_lastname, '-', b.ged_firstname, b.ged_lastname from tablename as a

Left join tablename as b on a.ged_id=b.ged_spouse_id

 

I have not tested this, and maybe it needs to be a right join instead. But let us know if it works or gets you closer. 

/Charlie (troubleshooter, carehart. org)
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
December 12, 2021

I would second Charlie's left-join suggestion, but with an ever so slightly different clause:

SELECT a.ged_firstname, a.ged_lastname, '-', b.ged_firstname, b.ged_lastname

FROM tablename AS a

LEFT JOIN tablename AS b ON a.ged_spouse_id=b.ged_id

S
seasonedwebAuthor
Inspiring
December 12, 2021

Thanks! So:

 

<cfquery name="match_spouse" datasource="#Request.BaseDSN#">
SELECT a.ged_firstname, a.ged_lastname, '-', b.ged_firstname, b.ged_lastname
FROM ged_main AS a
            LEFT JOIN ged_main AS b

            ON a.ged_spouse_id=b.ged_id
</cfquery>

 

<table width="900" border="1" class="borders2">

<tr class="trdynamic">
<td class="tdheaderdynamic">Last Name</td>
<td class="tdheaderdynamic">First Name</td>
<td class="tdheaderdynamic">Spouse Last Name</td>
<td class="tdheaderdynamic">Spouse First Name</td>

</tr>
<cfoutput query="match_spouse">

<tr>

<td class="tddynamic">#?#</td>

<td class="tddynamic">#?# </td>

<td class="tddynamic">#?#</td>

<td class="tddynamic">#?#</td>

</tr>

</cfoutput>

</table>

 

Would the output even lend itself to be dislpayed in table format? Thanks again.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices