Well I spoke too soon.  I am trying to join on another table and it is throwing a strange error - "The column prefix 'View2Tab1' does not match with a table name or alias name used in the query."

My join:

select t1.jobnum, t1.sequence, t2.sequence
from myjobs t1, myjobs t2
left outer join myparts t4 on t1.jobnum = t4.jobnum
where t1.jobnum = t2.jobnum and t2.sequence =
   (select top 1 t3.sequence from myjobs t3
    where t3.jobnum = t2.jobnum and t3.completed = 0)

Is the "myjobs t1, myjobs t2" statement causein this confic, perhaps?

Doug

Equal\inner joins can be specified in the FROM clause or the WHERE clause.  Outer joins have to be specified in the FROM clause, but you can't mix these two.  So, switch the inner/equal join to using the same type of syntax:

select t1.jobnum, t1.sequence, t2.sequence
from myjobs t1 inner join myjobs t2 on t1.jobnum = t2.jobnum
left outer join myparts t4 on t1.jobnum = t4.jobnum
where t2.sequence =
   (select top 1 t3.sequence from myjobs t3
    where t3.jobnum = t2.jobnum and t3.completed = 0)

At this point, we don't support order by in a column or table subquery.

I have been playing with a workaround in T-SQL this morning for my join issues.  I converted Linda's code to use a left outer join on the myjobs tables, like this:

select t1.jobnum, t1.sequence, t2.sequence, t4.partnum
from myjobs t1
left outer join myparts t4
on t1.jobnum = t4.jobnum
left outer join myjobs t2
on t1.jobnum = t2.jobnum and t2.sequence =
   (select min t3.sequence from myjobs t3
    where t3.jobnum = t2.jobnum and t3.completed = 0)

That seems to be the solution, but will try later with pSQL.

Doug