title: SQL Using subquery in where clause and use values in select
body: <p>I can not figure out how to get it done where I have a main select list, in which I want to use values which I select in a sub query in where clause..My query have join statements as well..loosely code will look like this</p>
  
  <pre><code>    if object_id('tempdb..#tdata') is not null drop table #tdata;
      go
  
      create table #tdata(
        machine_id varchar(12),
        temestamp datetime,
        commit_count int,
        amount decimal(6,2)
      );
  
      if object_id('tempdb..#tsubqry') is not null drop table #tsubqry;
      go
      --Edit:this is just to elaborate question, it will be a query that
      --will return data which I want to use as if it was a temp table
      --based upon condition in where clause..hope makes sense
      create table #tsubqry(
        machine_id varchar(12),
        temestamp datetime,
        amount1 decimal(6,2),
        amount2 decimal(6,2)
      );
  
  
      insert into #tdata select 'Machine1','2018-01-02 13:03:18.000',1,3.95;
      insert into #tdata select 'Machine1','2018-01-02 02:11:19.000',1,3.95;
      insert into #tdata select 'Machine1','2018-01-01 23:18:16.000',1,3.95;
  
      select m1.machine_id, m1.commit_count,m1.amount,***tsub***.amount1,***tsub***.amount2
  
      from #tdata m1, (select amount1,amount2 from #tsubqry where machine_id=#tdata.machine_id) as ***tsub***
      left join sometable1 m2 on m1.machine_id=m2.machine_id;
  </code></pre>
  
  <p>Edit: I have tried join but am getting m1.timestamp could not be bound as I need to compare these dates as well, here is my join statement</p>
  
  <pre><code>from #tdata m1
      left join (
          select amount1,amount2 from #tsubqry where cast(temestamp as date)&lt;=cast(m1.temestamp as date)
      ) tt on m1.machine_id=tt.machine_id
  </code></pre>
  
  <p>Problem is I want to use some values which has to be brought in from another table matching a criteria of main query and on top of that those values from another table has to be in the column list of main query..
  Hope it made some sense.
  Thanks in advance</p>