OK, I was able to successfully put together a LEFT JOIN with a MAX function using the coding scheme below. I'm still having a few issues so I am hoping to receive some helpful feedback concerning my problem.
- How do I call or retrieve other fields from the joined table in my output? Right now, I can only retrieve the field named AS field2 on my output. If I put b.field1 in my first SELECT statement it says it is invalid.
- What exactly is that b_max notation doing?
- If I join other fields on these two tables based on the max setup, do I have to use the b_max notation on all fields?
- Can I set other conditions for the max table after the ON statement?
FROM table1 a
LEFT JOIN ( SELECT b.table2_field1
, MAX(b.table2_field2) AS field200
FROM table2 b
WHERE b.table2_field3 = '2014'
GROUP BY b.table2_field1
) b_max ON b_max.table2_field1 = a.table1_field1
WHERE a.table1_field4 = 'Y'
I do not know if I understood all your doubts, anyway
If you want to use table name in object name (which is neat when joining multiple tables, keep doing it), you have to use the correct table/query alias name. In the main query, they are A for TABLE1 and B_MAX for the subquery on TABLE2. There is no table B in that scope. Also, there is no FIELD2 in the subquery, the correct names of its columns are B_MAX.TABLE2_FIELD1 and B_MAX.FIELD200.
Also, you may put compound condition (multiple conditions combined with AND or OR operator) into the ON clause. Referencing rule is the same as in the SELECT clause - A for TABLE1 and B_MAX for the subquery on TABLE2.