MySQL Select Unique Pairing of Names

Question

I am having trouble with a mysql query. I want to get a unique pairing of names that share the same tenant_group_id number. All you need to know is that each tenant has a unique individual_tenant_id and up to two individual tenants share the same tenant_group_id.

   SELECT t1.first_name, t2.first_name  
     FROM individualtenant t1  
LEFT JOIN individualtenant t2 ON t1.tenant_group_id = t2.tenant_group_id
                             AND t1.individual_tenant_id != t2.individual_tenant_id

Right now this is giving me pairs but both ways, for example, it would return "John", "Melissa" and "Melissa", "John" but I only need a unique pairing.

EDIT: And if the individualtenant does not have a partner I need to have NULL in the second column.

Answer 1

Try:

SELECT t1.first_name, t2.first_name
FROM individualtenant t1
LEFT JOIN individualtenant t2
ON t1.tenant_group_id = t2.tenant_group_id 
WHERE t1.individual_tenant_id < t2.individual_tenant_id 
    or t2.individual_tenant_id is null;

Answer 2

Doing it with a LEFT JOIN where a NULL could be either 'no second tenant' or 'matched on previous ordering of tenants' could be quite tricky, and I'm not sure how to handle those. However, if this is all the data you need, and there's no need to fetch further data from other tables, we could cheat a little bit (which would be a separated list of N values, 1 or more):

SELECT GROUP_CONCAT(first_name)
FROM individualtenant
GROUP BY tenant_group_id;

Another dirty, dirty cheat would be (with only 1 or 2 tenants):

SELECT MIN(first_name), IF(COUNt(*) > 1,MAX(first_name),NULL)
FROM individualtenant
GROUP BY tenant_group_id;

Answer 3

Is this what you seek?

SELECT
t1.first_name AS 'first_tenant',
t2.first_name AS 'second_tenant'
FROM
individualtenant t1,
individualtenant t2
WHERE
    t1.tenant_group_id = t2.tenant_group_id
AND t1.individual_tenant_id < t2.individual_tenant_id
UNION
SELECT
t1.first_name,
"No Match"
FROM
individualtenant t1
WHERE NOT EXISTS (
SELECT *
FROM individualtenant t2
WHERE
    t2.individual_tenant_id <> t1.individual_tenant_id
AND t2.tenant_group_id = t1.tenant_group_id
)