Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

im 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, and i was wondering how i could access that value in other queries without having to rerun it?

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
well, ideally i wouldnt limit the outer query, because i have other data im retrieving that wouldnt need this limiter –  wcpro Jan 5 '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 6 '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 6 '10 at 15:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.