If it helps, table A to table be is a one to many. I tried running the suggestion and I was getting no rows back. I only want to join when the max record is 'O', not the max record with 'O'. Keyword here is IF it has an 'O' as in open.
The way I understand what you want is this. Show the ID_NO if the row in table B with the max(STA_ID) has a STA_CD value of 'O', otherwise do not show th ID_NO. If this is the case, then with the data you have shown, you will get nothing back because the max(sta_cd) has a 'C". Now if you want the 2nd row returned, then the query would be:
select ... from tableA as A, tableB as b
where (a.ID_NO = b.ID_NO) and (b.sta_ID =
(select max(b2.sta_ID) from tableB as B2 where (b2.ID_NO = a.ID_NO) and (b2.STA_CD = 'O'))) and (b.STA_CD = 'O')