0

I wonder if you all can help. I have this query that gets max dated rows from the joined table. However, the join condition removes some entries from the output. Can this be solved so that results that have not been joined still show in the results as NULL or empty?

Currently removes rows from tbl1 results that have no joined entries in tbl2. I need all results with or without a max date or id on tbl2.

example: 50 rows with a.ac_id=1, but when I add the max date condition only 48 rows show in the result as they don't have an entry in tbl2 but are still in tbl1. So I still need all of the results to show for tbl1. I hope I have made myself clear.

SELECT a.si_id, a.as_type, a.as_id, b.bo_status, b.bo_id AS check_bo, a.f_term, UNIX_TIMESTAMP(MAX(b.bo_date)) AS bo_date, a.re_date, a.re_type, b.bo_date 
FROM tbl1 AS a 
LEFT JOIN tbl2 AS b ON a.as_id=b.as_id 
WHERE a.ac_id=1 AND b.bo_date = (
    SELECT MAX(bo_date) 
    FROM tbl2 
    WHERE as_id = a.as_id
) 
GROUP BY a.as_id
SelVazi
  • 10,028
  • 2
  • 13
  • 29
Tone
  • 19
  • 5
  • You have table b in the LEFT JOIN and the WHERE which makes it like an INNER JOIN. I'd try putting everything in the LEFT JOIN, if possible. – Rob Sedgwick Feb 26 '23 at 21:31
  • I think this is the way to go. If I find a solution will post it. – Tone Feb 27 '23 at 14:10
  • This thread guided me to the answer by moving the where to a left join.https://stackoverflow.com/questions/32265830/left-join-on-maxdate – Tone Mar 03 '23 at 14:12

0 Answers0