Merging Two Columns into One

The database in question contains the names, and the contact information of a number of family groups - one or more of the following (adult1, adult2, and children). Some of the adult members of these family groups have roles to play in the organization. I am trying to form a list of all adults with roles, and what their roles are.

I would rather keep all the family information in a single table record, rather than split it up into individuals and join the individuals together later into the family groups. I know this would have made my query below easier, but it also would have complicated my query for displaying the contact directory information for each family.

I used the following query to get all family groups with adults who had assigned roles and got (for example) the following data (first_name1 has role1 and first_name2 has role2):

SELECT last_name, first_name1, first_name2, role1, role2 
FROM contacts
WHERE role1 > 0 OR role2 > 0
ORDER BY last_name ASC

I would like to end up with the following result from my mySQL query:

Could someone please point me in the right direction? I am happy to try to figure this out, if I can get some advice as to where to start.

MySQL? This should work (you might not need the IFNULL since you didn’t say whether it can be null or not, but better safe than sorry…)

SELECT last_name
      , IF(IFNULL(role1, 0) > IFNULL(role2, 0), first_name1, first_name2) AS first_name
      , IF(IFNULL(role1, 0) > IFNULL(role2, 0), role1, role2) AS role
 FROM contacts
WHERE role1 > 0 
   OR role2 > 0
ORDER BY last_name ASC

It almost worked. The problem lies with the last record, Warren, where both members have roles. Only the second member was included.

Also, could you please explain what the comparison in IF(IFNULL(role1, 0) > IFNULL(role2, 0) does?

BTW, I have first_name2, role1 and role2 set to be NULL if no entry is made there. But I guess it wouldn’t hurt to be careful, like you said. :slight_smile:

Oops. Didn’t catch that. It first looked like you wanted to just see the higher totals of the two. In that case, a UNION would probably be easiest, though you’ll need to subquery it to order it by name

SELECT last_name
     , first_name
     , role
  FROM (SELECT last_name
             , first_name1 AS first_name
             , role1 AS role
         WHERE role1 > 0
        UNION ALL
        SELECT last_name
             , first_name2 AS first_name
             , role2 AS role
         WHERE role2 > 0) SQ
ORDER BY last_name ASC

It basically works like this: IF(COND, true, false)

If the condition being checked is true, the first value is returned, otherwise the second is returned

Okay, I didn’t understand it in the context of my question, but that’s because it wasn’t a requirement. Cool.

Could you please check the syntax of your query - I don’t know where to place FROM contacts in relation to the UNION part of the query. I tried a few things, but got a syntax error each time.

Okay, now I understand. That SQ was the name of the derived table - all is good now and that query works perfectly. Thank you. :smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.