Replies: 1 comment
-
Figured out what was happening, There was one Family that had 2 Spouses. Corrected that and it is able to run. |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
Hello, I am trying to get a specific SQL query to work. I want to get a CONCAT of the head of a Family in the format: Family Name, Head and Spouse.
I can currently get the query to give me the Family Name for every Person. I can also get the first name of the Head of Household tacked on after the Family Name, but when I try to grab the Spouse, the query breaks. I can only assume it is due to not every Head having a Spouse, but it doesn't break on a Person not having a Family. Does anyone have a suggestion?
The below query works and gives me 'FamilyName, Head and Head'. If I change WHERE spouse.per_fmr_ID = 1 to 2, it breaks.
SELECT
CONCAT(per.per_FirstName,' ',per.per_LastName) as 'Name',
CONCAT(fam.fam_Name,', ',
(SELECT head.per_FirstName
FROM person_per AS head
WHERE head.per_fam_ID = per.per_fam_ID && head.per_fmr_ID =1
),' and ',
(SELECT spouse.per_FirstName
FROM person_per AS spouse
WHERE spouse.per_fmr_ID = 1 && spouse.per_fam_ID = per.per_fam_ID
)
) as 'Family Name'
FROM person_per AS per
LEFT JOIN family_fam AS fam ON per.per_fam_ID = fam.fam_ID
Beta Was this translation helpful? Give feedback.
All reactions