Mysql left joins last row

Question

I am making a query in mysql to import users in our marketing system and need for this a lot of data like for example :

• Date of last order
• Date of last cheque created
• ...

This are all left joins where I just want the last result with conditions. For the moment I do just a select for this field but I don't know if this is the best option. Here you have the query that I'm currently usign:

select u.first_name, u.last_name, u.email, u.birthdate, u.id, u.sex, c.name, u.facebook_id, u.created_at, u.last_active, u.referrer_id,
IF(wants_email = 0, (select created_at from wants_email_logs where value = 0 and u.id = user_id order by id desc limit 1), NULL ) as DATEUNJOIN,
(select max(o.created_at) from orders o where o.user_id = u.id and o.state in ('paid','processing', 'shipped', 'completed')) as DATE_LAST_ORDER,
(select max(c.created_at) from cheques c where c.user_id = u.id and c.spent = 0) as cheque_date from users u
left join countries c on u.country_id = c.id ;

• user16567: Add this: order by left_table.required_field limit 1

Answer 1

Can you use temp tables? I think that's generally the best idea... using a lot of subqueries can get nasty real quick. Here's my version:

(I'm not able to test it so use it at your own risk. Also if you use temp tables, you'll still probably want to define your datatypes in the CREATE TABLE sections)

DROP TABLE IF EXISTS temp_date_unjoin;
CREATE TABLE temp_date_unjoin
SELECT
 user_id,
 MAX(created_at) AS date_unjoin
FROM wants_email_logs
WHERE value = 0
GROUP BY user_id;
DROP TABLE IF EXISTS temp_date_last_order;
CREATE TABLE temp_date_last_order
SELECT
 user_id,
 MAX(created_at) AS date_last_order
FROM orders o
WHERE state IN ('paid', 'processing', 'shipped', 'completed')
GROUP BY user_id;
DROP TABLE IF EXISTS temp_cheque_date;
CREATE TABLE temp_cheque_date
SELECT
 user_id,
 MAX(created_at) AS cheque_date
FROM cheques
GROUP BY user_id;
SELECT 
 u.first_name, 
 u.last_name, 
 u.email, 
 u.birthdate, 
 u.id, 
 u.sex, 
 c.name, 
 u.facebook_id, 
 u.created_at, 
 u.last_active, 
 u.referrer_id,
 du.date_unjoin,
 lo.date_last_order,
 cd.cheque_date 
FROM 
 users u
 LEFT JOIN temp_date_unjoin du
 ON du.user_id = u.id
 LEFT JOIN temp_date_last_order lo
 ON lo.user_id = u.id
 LEFT JOIN temp_cheque_date cd
 ON cd.user_id = u.id
 LEFT JOIN countries c 
 ON u.country_id = c.id 
;