Ordering results in a left join not working

Question

I have two tables, locations and locations_contacts.

mysql> describe locations;
+----------------+----------------------------+------+-----+---------+----------------+
| Field          | Type                       | Null | Key | Default | Extra          |
+----------------+----------------------------+------+-----+---------+----------------+
| location_id    | int(4)                     | NO   | PRI | NULL    | auto_increment |
| location_name  | varchar(100)               | YES  |     | NULL    |                |
+----------------+----------------------------+------+-----+---------+----------------+

mysql> describe locations_contacts;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| contact_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| location_id   | int(4)       | YES  |     | NULL    |                |
| contact_name  | varchar(100) | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

Each location can have more than one contact, but the first one entered - the one with the lowest-numbered contact_id - is regarded as the primary contact. I want to be able to pull the location name and primary contact with a single query. This is what worked initially:

select a.location_id, a.location_name, b.contact from locations as a
    left join (
        select location_id, contact_name as contact 
        from locations_contacts 
        group by location_id
        ) as b on (a.location_id = b.location_id)
where (location_name like '%wesleyan%' or contact like '%wesleyan%') 
order by location_name;

The problem is that the ordering of locations_contacts isn't consistent. While using InnoDB, it was fine, but this is now being hosted on an ndb cluster setup, and the results being returned inside the left join are inconsistent ordered, so if there are multiple contacts for a location I get an essentially random result. Using "order by" with the left join doesn't help, as grouping happens before it can be ordered, and I can't eliminate the group by clause, because I only want one record for each location returned.

Can anyone help me with this? I've been tearing out my hair for the last day or so on this, and I didn't exactly have a whole lot to spare.

It's possible, but I want that to be a last resort; I was hoping to solve it this way in case a similar issue crops up somewhere else. — loungehead, Sep 21 '17 at 15:57

Tim Biegeleisen · Accepted Answer · 2017-09-21T16:29:45.167

I think we need two joins here. The first joins to a subquery similar to what you had in your question. The difference is that it finds the lowest contact_id for each location. But you want the contact name, not the id, so we can do another join to the locations_contacts table to bring in the contact_name.

select
    a.location_id,
    a.location_name,
    c.contact_name
from locations as a
left join
(
    select location_id, min(contact_id) as min_contact_id 
    from locations_contacts 
    group by location_id
) as b
    on a.location_id = b.location_id
inner join locations_contacts c
    on b.location_id = c.location_id and
       c.contact_id = b.min_contact_id
where
    a.location_name like '%wesleyan%' or
    c.contact_name like '%wesleyan%'
order by a.location_name;

score 0 · Answer 2 · answered Sep 21 '17 at 16:13

I'm not sure this is the cleanest solution, but it should be possible to do with two joins. The first to get the minimum for the group and second to get the contact. Something like:

select a.location_id, a.location_name, c.contact from locations as a
    left join (
        select location_id, min(contact_id) 
        from locations_contacts 
        group by location_id
        ) as b on (a.location_id = b.location_id)
    left join (
        select contact_name as contact 
        from locations_contacts 
        ) as c on (b.location_id = c.location_id and b.contact_id = c.contact_id)
where (location_name like '%wesleyan%' or contact like '%wesleyan%') 
order by location_name;

Ordering results in a left join not working

2 Answers2