Joining to a table with multiple rows needing only the max key if status = 'O'.

The problem I am having is when I use Max(STA_ID) and I can't use where clause for STA_CD = 'O'. I only want to join "IF" the MAX(STA_ID) has Status code of 'O'.

Thanks in Advance, Marcel K. Data below would not yield a join, because the MAX(STA_ID) STA_CD = 'C'.


ID_NO STA_ID STA_CD
1085614000006 1999-03-18-23.16.39.945269 C
1085614000006 1999-03-18-11.59.40.284873 O
1085614000006 1999-03-17-10.39.20.630029 O
1085614000006 1999-03-11-06.57.31.405619 O

DB2 Version is 7.1.1

I think this is what you want:

Assumptions:
tableA = parent table
TableB as described in your post

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 (b.STA_CD = 'O')

HTH

Andy

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.

thanks.

Woops, I missed a closing parenthesis ")"

try:

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 (b.STA_CD = 'O')

Andy

Still not flying it doesn't error out, but no data is coming back.

Thanks for your input.

ID_NO STA_ID STA_CD
1085614000006 1999-03-18-23.16.39.945269 C
1085614000006 1999-03-18-11.59.40.284873 O
1085614000006 1999-03-17-10.39.20.630029 O
1085614000006 1999-03-11-06.57.31.405619 O

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')

Andy