MYSQL join not selecting correct row

Question

I have two tables

1) outreach

id  profile_id  url
-------------------------
1    2        www.test.com
2    3        www.google.com 
3    4        www.example.com

2). outreach_links

id  outreach_id  end_date             status
------------------------------------
1    1           2016-12-28 00:00:00  Approved
2    1           2016-12-16 00:00:00  Approved
3    1           NUll                 Pending
4    1           2016-12-11 00:00:00  Approved

I have this SQL Query with Left Join and Conditions that is working fine except I want to select the whole ROW of the MAX end_date that meets the 3 condition. so in this case the first row with end_date = 2016-12-28 00:00:00

select o.*,ol.*,MAX(ol.end_date) as max_date, SUM(ol.status = "Approved" and (ol.end_date > Now() or end_date is null)) as cond1, SUM(ol.status = "Pending") as cond2,
    SUM(ol.status = "Approved" and (ol.end_date < Now() and ol.end_date is not null)) as cond3
FROM outreach o 
LEFT JOIN outreach_links ol on ol.outreach_id = o.id 
WHERE o.profile_id=2 
GROUP BY o.id
HAVING (cond1 = 0 and cond2 = 0) or (cond1 = 0 and (cond2 = 1 and cond3 >=1)) 
ORDER BY ol.end_date desc

but this is the output for this query ( its picking the pending for some reason) >>

+"id": "3"
+"profile_id": "2"
+"url": "www.test.com"
+"outreach_id": "1"
+"end_date": null
+"status": "Pending"
+"max_date": "2016-12-28 00:00:00"
+"cond1": "0"
+"cond2": "1"
+"cond3": "3"

I want to get this instead

+"id": "1"
+"profile_id": "2"
+"url": "www.test.com"
+"outreach_id": "1"
+"end_date": 2016-12-28 00:00:00
+"status": "Approved"
+"max_date": "2016-12-28 00:00:00"
+"cond1": "0"
+"cond2": "1"
+"cond3": "3"

The first row with MAX end date, how can I do that keeping this same Query ??

Thanks

FYI, boolean expressions implicitly evaluate to 1 and 0, so you don't need the `IF`. — shmosel, Dec 29 '16 at 22:26
I am just saying for my case , Cond3 could have more than one matches thats why >=1 and at least one — user3150060, Dec 29 '16 at 22:31
You're using `SUM()` without `GROUP BY`, so it sums all the rows that match the `WHERE` clause, and the rest of the columns come from a random row that matches. — Barmar, Dec 29 '16 at 22:31
@user3150060 He's just saying you can do `SUM(ol.status = "Approved" and (ol.end_date < Now() and ol.end_date is not null)`, you don't need to do `SUM(IF(..., 1, 0))` — Barmar, Dec 29 '16 at 22:32
Got it I @Barmar , I see your point I can change that but still that doesnt fix my problem how can I get the row with MAX end_date — user3150060, Dec 29 '16 at 22:33
I am just saying you don't need the `IF`. In other words, `SUM(condition)` is equivalent to `SUM(IF(condition, 1, 0))`. — shmosel, Dec 29 '16 at 22:33
Didn't see it because you put it on the same line as something else. I've reformatted the query to be more readable. — Barmar, Dec 29 '16 at 22:33
You get `status = Pending` because it's grabbing that column from an arbitrary row in the group. Any columns that aren't in aggregate functions can't be predicted which row they'll come from. — Barmar, Dec 29 '16 at 22:37
How do I make it select out of these 4 the max end_date is that possible — user3150060, Dec 29 '16 at 22:38
See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the row with the max value of a column in each group. You'll need to join with that subquery. — Barmar, Dec 29 '16 at 22:39
is it possible to make something like this SELECT a.id, a.rev, a.contents FROM YourTable a INNER JOIN ( SELECT id, MAX(rev) rev FROM YourTable GROUP BY id ) b ON a.id = b.id AND a.rev = b.rev and also use "having"? — user3150060, Dec 29 '16 at 22:42

score 1 · Accepted Answer · edited May 23 '17 at 12:08

See SQL Select only rows with Max Value on a Column for how to get the row with the max end_date for each outreach_id. Then join with that row to get the latest status.

SELECT o.*, ol1.max_date, ol2.status, SUM(ol.status = "Approved" and (ol.end_date > Now() or end_date is null)) as cond1, SUM(ol.status = "Pending") as cond2,
    SUM(ol.status = "Approved" and (ol.end_date < Now() and ol.end_date is not null)) as cond3
FROM outreach o 
LEFT JOIN outreach_links AS ol ON ol.outreach_id = o.id
LEFT JOIN (SELECT outreach_id, MAX(end_date) AS max_date
           FROM outreach_links
           GROUP BY outreach_id) AS ol1 ON ol1.outreach_id = o.id
LEFT JOIN outreach_links ol2 on ol2.outreach_id = o.id AND ol2.end_date = ol1.max_date
WHERE o.profile_id=2 
GROUP BY o.id
HAVING (cond1 = 0 and cond2 = 0) or (cond1 = 0 and (cond2 = 1 and cond3 >=1)) 
ORDER BY ol.end_date desc

Strange getting SQL error on line one : check the manual that corresponds to your MySQL server version for the right syntax to use near 'o.*, ol1.max_date, ol2.status, SUM(ol.status = ? and (ol.end_date > ? or ol.end_' at line 1 — user3150060, Dec 29 '16 at 23:12
I can't reproduce that error. I suspect you have an extra character near `o.*`. — Barmar, Dec 29 '16 at 23:18

MYSQL join not selecting correct row

1 Answers1