Improving concatenated Left join queries in a single result row

Question

I have two tables; members and telephone.

A member can have multiple telephone rows. Each telephone row can have flags set to indicate what type of phone number it is.

I need a result set that is a single row per member, containing all or some of the different telephone rows.

This is a concatenation of LEFT JOINS from different parts of the same table.

The database tables both have full indexes on all numeric columns.

Table structures

(probably not that important but here for completeness sake)

members:

CREATE TABLE `members` (
 `mem_id` smallint(6) NOT NULL AUTO_INCREMENT,
 ...
 `reg` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'No Reg',
 PRIMARY KEY (`mem_id`)
) ENGINE=MyISAM AUTO_INCREMENT=968 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Telephone:

CREATE TABLE `telephone` (
 `tel_id` int(8) NOT NULL AUTO_INCREMENT,
 `mem_id` int(8) NOT NULL,
 `tel_name` varchar(64) CHARACTER SET utf8 NOT NULL,
 `tel_num` varchar(24) COLLATE utf8mb4_unicode_ci NOT NULL,
 `main` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `player` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `home` char(1) CHARACTER SET utf8 NOT NULL DEFAULT 'N' COMMENT 'Y= Home Phone',
 PRIMARY KEY (`tel_id`),
 KEY `memid` (`mem_id`),
 KEY `main` (`main`),
 KEY `player` (`player`),
 KEY `home` (`home`),
 KEY `telnum` (`tel_num`)
) ENGINE=MyISAM AUTO_INCREMENT=2237 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

---

My Query so far:

Aim of the query is to return a single row for each member detailing (amongst other data unshown) all their associated telephone numbers and being able to differenciate between each number:

The WHEREconditionals are only applied to the members table only.

SELECT members.mem_id, ... ,
       thome.tel_num as thome, tmob.tel_num as tmob, 
       twork.tel_num as twork
FROM members
LEFT JOIN telephone thome FROM telephone ON thome.mem_id = members.mem_id AND thome.home = 'Y'
LEFT JOIN telephone tmob FROM telephone ON tmob.mem_id = members.mem_id AND tmob.main = 'Y' AND tmob.tel_num LIKE '07%'
LEFT JOIN telephone twork FROM telephone ON twork.mem_id = members.mem_id AND twork.player = 'Y'
WHERE ...   

---

Example data:

Members table:

 memid | mname   |  reg 
---------------------------------
  22   | george  | 1456436456

Telephone table:

  tel_id | mem_id |   tel_name  |   tel_num    | main | player | home
-----------------------------------------------------------------------
     1   |   22   |  Home phone | 01234 456463 |  N   |   N    |  Y
     12  |   22   |  Work phone | 01334 457893 |  Y   |   N    |  N
     19  |   22   |  Mobile num | 07564 456333 |  N   |   Y    |  N
     23  |   22   |  Home phone | 01987 657353 |  N   |   N    |  N

Desired Result:

 memid | mname  |     reg    |  twork  |  tmob   |  thome  | etc...   
-------------------------------------------------------------------
  22   | george | 1456436456 | 01334...| 07564...| 01234...| ...

---

Notes:

• I'm aware that the flag columns (main, player, etc.) could be ENUMs

• I'm also aware the tables could be InnoDB and have foreign keys setup.

(I'm not at all against either of these things it's just a case of getting round to them)

• None of the telephone numbers are required so while I am aware INNER JOIN is more efficient; it can't be used in this case.

• GROUP_CONCAT doesn't work on a per-column basis as each column is selected by differet criteria (flags). Maybe it can work on a broader setting but each tel_num value would need to be renamed (I think I could use some guidance on using GROUP_CONCAT in this situation).

• Flags are not mutually exclusive: a telephone row could be | Y | Y | Y . But each member only has one of each flag.

• All the phone numbers are active, some will have no flags and so not need to be returned.

---

Question:

Can I improve my SQL query to not need three seperate LEFT JOINs?

---

Other questions I've looked at with similar issues:

• Mysql - optimisation - multiple group_concat & joins using having

• INNER or LEFT Joining Multiple Table Records Into A Single Row

• MySQL: combining multiple values after a join into one result column

Answer 1

Without changing your current schema, I don't see a way to improve your current query, other than perhaps trying to tune it with indices etc. However, I can make a suggested change in your table design which can lend itself to a much simpler query for the output you want. Consider refactoring the telephone table to the following:

  tel_id | mem_id |   tel_name  |   tel_num    | type | active
-----------------------------------------------------------------------
     1   |   22   |  Home phone | 01234 456463 |  1   | 1
     12  |   22   |  Work phone | 01334 457893 |  2   | 1
     19  |   22   |  Mobile num | 07564 456333 |  3   | 1
     23  |   22   |  Home phone | 01987 657353 |  1   | 0

Now if you want a query which will retrieve the active home, work, and mobile numbers for each member, the following should suffice:

SELECT
    m.memid,
    m.mname,
    m.reg,
    GROUP_CONCAT(t.tel_name ORDER BY t.type) names,
    GROUP_CONCAT(t.tel_num ORDER BY t.type) numbers
FROM members m
LEFT JOIN telephone t
    ON m.mem_id = t.mem_id AND
       t.active = 1
GROUP BY
    m.memid;

I have assumed here that each member will always have some entry for the home, work, and mobile numbers, even if that entry should be null or empty string. I also assume that you only want to report the active numbers. If you need to report all numbers, then a group concat solution becomes less attractive, because you might get back a long CSV string and it might not be intuitive how to read it.

Edit:

We can also GROUP_CONCAT the telephone name labels. This means that each record in the result set will have, in addition to a CSV list of numbers, the corresponding phone number types.