MySQL Forums
Forum List  »  Newbie

Query that builds a Household value based on address and age
Posted by: Bill Carney
Date: January 30, 2023 10:42AM

I’m trying to write a query and I just can’t wrap my head around it. I want to create a query that builds a HouseholdName value based on address and age criteria. My biggest issue is concatenating the FName values from two different records.


MyTable, simplified to relevant fields:

FName,LName,Address,City,Age
John,Hancock,234 Road,Quincy,45
Dorothy,Hancock,234 Road,Quincy,40
Lydia,Hancock,234 Road,Quincy,12
John,Adams,123 Main,New York,39
Jane,Adams,123 Main,New York,36
Tom,Jefferson,456 Ave,Philly,42
Polly,Jefferson,456 Ave,Philly,6
Ben,Franklin,789 Street,Pittsburg,74

Basically I want to create a query that creates a Household value based on:

• If 3+ people at the same address and city with the same last name, Household = “The [LName] Family”
• If 2 people at the same address and city with the same last name AND both ages are within 18 years of each other, then Household = BOTH person’s FName and Lname.
• If 2 people at the same address and city with the same last name and both ages are not within 18 years of each other, then Household = “[FName] [Lname]” for each person.
• If 1 peron at address and city, then Household = “[FName] [Lname]”.

18 years being an arbitrary number to assume they are married.

So when I run the query my results should be

FName,LName,Address,City,Age,HHName
John,Hancock,234 Road,Quincy,45,The Hancock Family
Dorothy,Hancock,234 Road,Quincy,40, The Hancock Family
Lydia,Hancock,234 Road,Quincy,12, The Hancock Family
John,Adams,123 Main,New York,39,John and Jane Adams
Jane,Adams,123 Main,New York,36, John and Jane Adams
Tom,Jefferson,456 Ave,Philly,42,Tom Jefferson
Polly,Jefferson,456 Ave,Philly,6,Polly Jefferson
Ben,Franklin,789 Street,Pittsburg,74,Ben Franklin

Any ideas on how to accomplish this?

I figured this involves some sort of nested queries, but I just can't think on how to use the value of another record to build the "John and Jane Adams" results (among other difficulties).

Options: ReplyQuote


Subject
Written By
Posted
Query that builds a Household value based on address and age
January 30, 2023 10:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.