0

I am trying to build a query that has something like this

select
    id,
    (select top 1 create_date from table2 
         where table1id = t1.id 
         and status = 'success') [last_success_date],
    (select count(*) from table2
         where table1id = t1.id
         and create_date > [last_success_date]) [failures_since_success]
from table1 t1

As you can see, the [last_Success_Date] is not within the scope of the second query. I was wondering how could I access that value in other queries without having to rerun it?

peterh
  • 4,953
  • 13
  • 30
  • 44
Rod Johnson
  • 453
  • 1
  • 4
  • 7

1 Answers1

1

You could include your corresponding sub query for [last_success_date] in the 'from' rather than the 'select'. When you do so you'll probably join on table 1 id. This will make the value available for 'select'.

L.Moser
  • 153
  • 1
  • 5
  • well, ideally i wouldnt limit the outer query, because i have other data im retrieving that wouldnt need this limiter – Rod Johnson Jan 05 '10 at 23:40
  • Not sure exactly what your data looks like, but could you left join from table1 to table 2? Without seeing your data, it looks like this is what you're already doing. With the exception that a left join in the outer query would make the scope of your variable larger. – L.Moser Jan 06 '10 at 15:14
  • I should have been clearer in that last comment. By using a field from table 1 to filter table 2 within the select. This, in addition to the 'top 1', it is effectively doing a left join from table 1 to 2. – L.Moser Jan 06 '10 at 15:47