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
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.