SELECT DISTINCT
  T1.[id] AS "index",
  T1.[Family_ID] AS "Fam_id",
  REPLACE([MART_SubjectInformation].[subject_code],'.','') AS "subject_code",
  CASE
    WHEN [MART_SubjectInformation].[sex] = 'Male' THEN 'm'
    WHEN [MART_SubjectInformation].[sex] = 'Female' THEN 'f'
    ELSE [MART_SubjectInformation].[sex]
  END AS "sex",
  CASE
    WHEN T1.[Affected_status] = 'Affected' THEN '1'
    WHEN T1.[Affected_status] = 'Unaffected' THEN '0'
    ELSE T1.[Affected_status]
  END AS "affected",
  CASE
    WHEN [MART_SubjectInformation].[mother_code] IS NOT NULL THEN REPLACE([MART_SubjectInformation].[mother_code],'.','') --+'subjrec' --use this to visualize which data source the parent subject code is coming from
    WHEN T3.[subject_code] IS NULL THEN 'NA'
    ELSE REPLACE(T3.[subject_code],'.','')
  END AS "mom",
  CASE
    WHEN [MART_SubjectInformation].[father_code] IS NOT NULL THEN REPLACE([MART_SubjectInformation].[father_code],'.','') --+'subjrec' --use this to visualize which data source the parent subject code is coming from
    WHEN T2.[subject_code] IS NULL THEN 'NA'
    ELSE REPLACE(T2.[subject_code],'.','')
  END AS "dad",
  CASE
    WHEN [MART_SubjectInformation].[mortality_status] = 'Alive' THEN '1'
    WHEN [MART_SubjectInformation].[mortality_status] = 'Dead' THEN '0'
    ELSE [MART_SubjectInformation].[mortality_status]
  END AS "alive"
FROM [MART_SubjectInformation]
INNER JOIN [MART_EX_FORM_UDP_next_gen_seq_status] T1
  ON [MART_SubjectInformation].[subject_id] = T1.[subject_id]
LEFT JOIN [MART_EX_FORM_UDP_next_gen_seq_status] T2
  ON T1.[Family_ID] = T2.[Family_ID]
  AND T1.[Relationship] NOT IN ('Father','Mother')
  AND T2.[Relationship] = 'Father'
LEFT JOIN [MART_EX_FORM_UDP_next_gen_seq_status] T3
  ON T1.[Family_ID] = T3.[Family_ID]
  AND T1.[Relationship] NOT IN ('Father','Mother')
  AND T3.[Relationship] = 'Mother'
ORDER BY T1.[Family_ID], "subject_code"