I have three tables:
Now i want to have a list of all items with the first order for that item that has status B.
Result should be:
I came up with this code, but is there another/better way to achive this?
Code:
Item
4251
4252
4253
Code:
Indx Item Order
1 4251 100
2 4251 105
3 4251 110
4 4251 115
5 4252 200
6 4252 205
7 4252 210
8 4253 300
Code:
Order Stat
100 A
105 A
110 B
115 B
200 A
205 A
210 B
300 B
Result should be:
Code:
Item Order
4251 110
4252 210
4253 300
Code:
for each tbl_a no-lock,
first tbl_b no-lock
where tbl_b.item = tbl_a.item
and can-find(tbl_c where tbl_c.order = tbl_b.order
and tbl_c.stat = "B")
use-index indx_b,
each tbl_c no-lock where tbl_c.order = tbl_b.order:
display tbl_a.item tbl_c.order.
end.