I have a table where I have the following headings,

Child_first_name, Carer1_Name, Carer2_Name, Carer1_Contact, carer2_Contact, Carers_Together.

I have created a view that reduces this down to the following Parents,Parent_Contact,Children where I've used a group concat to create the table below

I have a table where I have the following headings,

Child_first_name, Carer1_Name, Carer2_Name, Carer1_Contact, carer2_Contact, Carers_Together.

I have created a view that reduces this down to the following Parents,Parent_Contact,Children where I've used a group concat to create the table below

I have it so that it will work when the parents are together it will display as Parent1 & Parent2 and the contact column will display as parent1_contact,Parent2 contact when they are together and have accounted for either of those values being absent.

What I'm struggling to do is to work out how to separate this data into multiple rows to account for the Carers being separated yet both parties needing to be kept up to date without sending a joint email.

Ideally in this instance I would be able to create a view which did the following:

I have looked at other resources and found ways to split data across rows where the data was all initially in one column. I did try to concat the initial data into the one column, and then split it across the rows, but that gave me an sql error and I couldn't work out why- the only explanation it gave was that it didn't match my version of sql. I'm relatively new to sql- and everything I can do is based primarily from looking things up in order to do what I want to do to make this one project work. It took me over a year to get the table to this state. In addition- all examples I found had an id which was in the original table to pair the split data with- Whereas the data that I would like to pair the split data with is formed by the group_concat of the first_names.

My current code is below where 'tbc' will be the part that will hopefully split carer_1. Please five any sloppy code or if I've missed something obvious in my research. I am willing to confess I am way outside my depth here.

SELECT
    (
        CASE WHEN(
            (
                `Table_Name`.`carers_together` = '1'
            ) AND(
                LENGTH(
                    `Table_Name`.`Carer1`
                ) > 0
            ) AND(
                LENGTH(
                    `Table_Name`.`Carer2`
                ) > 0
            )
        ) THEN CONCAT_WS(
            ' & ',
            `Table_Name`.`Carer1`,
            `Table_Name`.`Carer2`
        ) WHEN(
            (
                `Table_Name`.`carers_together` = '0'
            ) AND(
                LENGTH(
                    `Table_Name`.`Carer1`
                ) > 0
            ) AND(
                LENGTH(
                    `Table_Name`.`Carer2`
                ) > 0
            )
        ) THEN 'tbc' WHEN(
            `Table_Name`.`Carer1` = ''
        ) THEN `Table_Name`.`Carer2` ELSE `Table_Name`.`Carer1`
    END
) AS `Carers`,
(
    CASE WHEN(
        (
            `Table_Name`.`carers_together` = '1'
        ) AND(
            LENGTH(
                `Table_Name`.`Carer1_Contact`
            ) > 0
        ) AND(
            LENGTH(
                `Table_Name`.`Carer2_Contact`
            ) > 0
        )
    ) THEN CONCAT_WS(
        ',',
        `Table_Name`.`Carer1_Contact`,
        `Table_Name`.`Carer2_Contact`
    ) WHEN(
        (
            `Table_Name`.`carers_together` = '0'
        ) AND(
            LENGTH(
                `Table_Name`.`Carer1_Contact`
            ) > 0
        ) AND(
            LENGTH(
                `Table_Name`.`Carer2_Contact`
            ) > 0
        )
    ) THEN 'tbc' WHEN(
        `Table_Name`.`Carer1_Contact` = ''
    ) THEN `Table_Name`.`Carer2_Contact` ELSE `Table_Name`.`Carer1_Contact`
END
) AS `Contact`,
GROUP_CONCAT(
    `Table_Name`.`First_Name` SEPARATOR ' & '
) AS `Children`
    GROUP BY
        `Table_Name`.`Carer1`,
        `Table_Name`.`Carer2`,
        `Table_Name`.`Carer1_Contact`,
        `Table_Name`.`Carer2_Contact`,
        `Table_Name`.`carers_together`

• mysql

• Read Tips for asking a good Structured Query Language (SQL) question, perform #5 and #3. – Akina Commented Feb 10 at 19:23
• 1 You should normalize the schema so you don't have duplicate columns like this. Create a separate Carers table with a row for each carer and foreign keys to the Children` table. – Barmar Commented Feb 10 at 19:41
• Use UNION to split into separate rows. One query gets the Carer1 info, another query gets the Carer2 info. Then combine them with UNION. – Barmar Commented Feb 10 at 19:44
• Thank you for the suggestion with splitting into two tables. This helped-I hadn't considered multiple tables because I didn't think you could have foreign keys in two columns of a table and wasn't sure how to use them. – Bemberlina Commented Feb 11 at 21:00

1 Answer 1

I'd just join an ad hoc table to specify which carer, like:

select
    if(carer.is1,t.carer1_name,t.carer2_name) carer_name,
    if(carer.is1,t.carer1_contact,t.carer2_contact) carer_contact,
    ...
from table_with_columns_for_both t
join (select 1 is1 union all select 0) carer
    # optionally, ignore carer1 or carer2 if empty/null
    on length(if(carer.is1,t.carer1_name,t.carer2_name))

(All the grouping stuff seems extraneous to your basic question.)