mysql left join table 1 to table 2 by id where table 2 id newest date

Question

I have two tables, table one

+----------+------------+------+-----+---------+----------------+
| Field    | Type       | Null | Key | Default | Extra          |
+----------+------------+------+-----+---------+----------------+
| id       | int(11)    | NO   | PRI | NULL    | auto_increment |
| pixel    | text       | NO   |     | NULL    |                |
| metadata | text       | NO   |     | NULL    |                |
| data     | text       | NO   |     | NULL    |                |
| attended | int(11)    | NO   |     | NULL    |                |
| alerted  | int(11)    | NO   |     | NULL    |                |
| date     | bigint(20) | NO   |     | NULL    |                |
+----------+------------+------+-----+---------+----------------+

Table two

+------------------+------------+------+-----+---------+----------------+
| Field            | Type       | Null | Key | Default | Extra          |
+------------------+------------+------+-----+---------+----------------+
| id               | int(11)    | NO   | PRI | NULL    | auto_increment |
| lead_id          | int(11)    | NO   |     | NULL    |                |
| project_id       | int(11)    | NO   |     | NULL    |                |
| user_id          | int(11)    | NO   |     | NULL    |                |
| type             | int(11)    | NO   |     | NULL    |                |
| status           | int(11)    | NO   |     | NULL    |                |
| text             | text       | NO   |     | NULL    |                |
| comm             | int(11)    | NO   |     | NULL    |                |
| sum              | text       | NO   |     | NULL    |                |
| meeting_date     | text       | NO   |     | NULL    |                |
| meeting_location | text       | NO   |     | NULL    |                |
| date             | bigint(20) | NO   |     | NULL    |                |
+------------------+------------+------+-----+---------+----------------+

I want to join table one to two on 1.id = 2.lead_id where 2.date is max and then select it if 2.status equals x

I tried, about everything (accept the answer :D ), nothing got it. Thank you. this question is different It has an AND cluse at the end

possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/q/7745609/1503018). But you need additional left join. — sectus, Dec 02 '14 at 14:19
Why do you want to join 1 to 2 if the only datas and conditions are on 2 (MAX(date) and status = "x") ? — pistou, Dec 02 '14 at 14:23

score 0 · Answer 1 · answered Dec 02 '14 at 14:32

0

SELECT one.*, two.*
FROM one left join two on one.id=two.lead_id 
WHERE MAX(two.date) and two.status='x'

answered Dec 02 '14 at 14:32

Suchit kumar

11,809
3
22
44

score 0 · Answer 2 · answered Dec 02 '14 at 14:37

When you only want the min/max of a single value as a condition, you can use ORDER BY and LIMIT 0,1 to get a single row :)

SELECT *
FROM table_one
INNER JOIN (
    SELECT *
    FROM table_two
    WHERE status = XXXXXXXXX
    ORDER BY date DESC
    LIMIT 0,1
) ON table_one.id = lead_id

mysql left join table 1 to table 2 by id where table 2 id newest date

2 Answers2