Question Three tables query

[nineinch]

I have three tables:

Item
4251
4252
4253

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

Order    Stat
100    A
105    A
110    B
115    B
200    A
205    A
210    B
300    B

Now i want to have a list of all items with the first order for that item that has status B.
Result should be:

Item    Order
4251    110
4252    210
4253    300

I came up with this code, but is there another/better way to achive this?

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.

 

[Rob Fitzpatrick]

It's hard to know what "first" means without knowing the indexes on the order table.

 

[nineinch]

tbl_a has unique index on tbl_a.item
tbl_b has unique index on tbl_b.indx
tbl_c has unique index on tbl_c.order

 

[Cringer]

I have three tables:

Item
4251
4252
4253

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

Order    Stat
100    A
105    A
110    B
115    B
200    A
205    A
210    B
300    B

Now i want to have a list of all items with the first order for that item that has status B.
Result should be:

Item    Order
4251    110
4252    210
4253    300

I came up with this code, but is there another/better way to achive this?

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.

Surely you can just do something like

for each a no-lock, 
  first b no-lock
  where b.item eq a.item,
  first c no-lock
  where c.order eq b.order
  and c.status eq 'B'
  break by a.item:
  if first-of(a.item) then 
    display a.item c.order. 
end.

Whatever the case, your solution is bad in terms of performance as the can-find will result in a table scan.

 

[nineinch]

I expected that to work too, but it doesn't.
My first try was this:

for each tbl_a no-lock,
    first tbl_b no-lock where tbl_b.item = tbl_a.item,
    each tbl_c no-lock where tbl_c.order = tbl_b.order
                         and tbl_c.stats = "B":
    display tbl_a.item tbl_c.order.
end.

This only reports item 4253 with order 300 as does your version.
Changing it to this i get the desired items and orders

for each tbl_a no-lock,
    each tbl_b no-lock where tbl_b.item = tbl_a.item,
    each tbl_c no-lock where tbl_c.order = tbl_b.order
                         and tbl_c.stats = "B":
break by tbl_b.item by tbl_b.indx:
    if first-of(tbl_b.item) then
        display tbl_a.item tbl_c.order.
end.

 

[TomBascom]

Using the FIRST keyword is pointless and silly in this example.

Specifying the ordering with BY is exactly the correct way to do it.