MySQL complex semi-join without group by

Question

Summary

I am looking for a semi-join(ish) query that selects a number of customers and joins their most recent data from other tables.

At a later time, I wish to directly append conditions to the end of the query: WHERE c.id IN (1,2,3)

Problem

As far as I am aware, my requirement rules out GROUP BY:

SELECT * FROM customer c
LEFT JOIN customer_address ca ON ca.customer_id = c.id
GROUP BY c.id
# PROBLEM: Cannot append conditions *after* GROUP BY!

With most subquery-based attempts, my problem is the same.

As an additional challenge, I cannot strictly use a semi-join, because I allow at least two types of phone numbers (mobile and landline), which come from the same table. As such, from the phone table I may be joining multiple records per customer, i.e. this is no longer a semi-join. My current solution below illustrates this.

Questions

• The EXPLAIN result at the bottom looks performant to me. Am I correct? Are each of the subqueries executed only once? Update: It appears that DEPENDENT SUBQUERY is executed once for each row in the outer query. It would be great if we could avoid this.
• Is there a better solution to what I am doing?

DDLs

DROP TABLE IF EXISTS customer;

CREATE TABLE `customer` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS customer_address;

CREATE TABLE `customer_address` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `street` varchar(85) DEFAULT NULL,
  `house_number` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
  );

DROP TABLE IF EXISTS customer_phone; 
CREATE TABLE `customer_phone` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `phone` varchar(32) DEFAULT NULL,
  `type` tinyint(3) unsigned NOT NULL COMMENT '1=mobile,2=landline',
  PRIMARY KEY (`id`)
  );

insert ignore customer values (1);
insert ignore customer_address values (1, 1, "OldStreet", 1),(2, 1, "NewStreet", 1);
insert ignore customer_phone values (1, 1, "12345-M", 1),(2, 1, "12345-L-Old", 2),(3, 1, "12345-L-New", 2);

SELECT * FROM customer;
+----+
| id |
+----+
|  1 |
+----+

SELECT * FROM customer_address;
+----+-------------+-----------+--------------+
| id | customer_id | street    | house_number |
+----+-------------+-----------+--------------+
|  1 |           1 | OldStreet |            1 |
|  2 |           1 | NewStreet |            1 |
+----+-------------+-----------+--------------+

SELECT * FROM customer_phone;
+----+-------------+-------------+------+
| id | customer_id | phone       | type |
+----+-------------+-------------+------+
|  1 |           1 | 12345-M     |    1 |
|  2 |           1 | 12345-L-Old |    2 |
|  3 |           1 | 12345-L-New |    2 |
+----+-------------+-------------+------+

Solution so far

SELECT *
FROM customer c

# Join the most recent address
LEFT JOIN customer_address ca ON ca.id = (SELECT MAX(ca.id) FROM customer_address ca WHERE ca.customer_id = c.id)

# Join the most recent mobile phone number
LEFT JOIN customer_phone cphm ON cphm.id = (SELECT MAX(cphm.id) FROM customer_phone cphm WHERE cphm.customer_id = c.id AND cphm.`type` = 1)

# Join the most recent landline phone number
LEFT JOIN customer_phone cphl ON cphl.id = (SELECT MAX(cphl.id) FROM customer_phone cphl WHERE cphl.customer_id = c.id AND cphl.`type` = 2)

# Yay conditions appended at the end
WHERE c.id IN (1,2,3)

Fiddle

This fiddle gives the appropriate result set using the given solution. See my questions above.

http://sqlfiddle.com/#!9/98c57/3

Answer 1

I would avoid those dependent subqueries, instead try this:

SELECT
      *
FROM customer c
      LEFT JOIN (
            SELECT
                  customer_id
                , MAX(id) AS currid
            FROM customer_phone
            WHERE type = 1
            GROUP BY
                  customer_id
      ) gm ON c.id = gm.customer_id
      LEFT JOIN customer_phone mobis ON gm.currid = mobis.id
      LEFT JOIN (
            SELECT
                  customer_id
                , MAX(id) AS currid
            FROM customer_phone
            WHERE type = 2
            GROUP BY
                  customer_id
      ) gl ON c.id = gl.customer_id
      LEFT JOIN customer_phone lands ON gl.currid = lands.id
WHERE c.id IN (1, 2, 3)
;

or, perhaps:

SELECT
      *
FROM customer c
      LEFT JOIN (
            SELECT
                  customer_id
                , MAX(case when type = 1 then id end) AS mobid
                , MAX(case when type = 2 then id end) AS lndid
            FROM customer_phone
            GROUP BY
                  customer_id
      ) gp ON c.id = gp.customer_id
      LEFT JOIN customer_phone mobis ON gp.mobid = mobis.id
      LEFT JOIN customer_phone lands ON gp.lndid = lands.id
WHERE c.id IN (1, 2, 3)
;

see: http://sqlfiddle.com/#!9/ef983/1/