Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.birthdate,,,, 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 = user_id order by id desc limit 1), NULL ) as DATEUNJOIN,
(select max(o.created_at) from orders o where o.user_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 = and c.spent = 0) as cheque_date from users u
left join countries c on u.country_id = ;
share|improve this question
Add this: order by left_table.required_field limit 1 – user16567 Sep 14 '12 at 19:28

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
    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
    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
    MAX(created_at) AS cheque_date
FROM cheques
GROUP BY user_id;

    users u
    LEFT JOIN temp_date_unjoin du
        ON du.user_id =
    LEFT JOIN temp_date_last_order lo
        ON lo.user_id =
    LEFT JOIN temp_cheque_date cd
        ON cd.user_id =
    LEFT JOIN countries c 
        ON u.country_id = 
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.