CODEWITHSUNDEEP
sqlsql-server
user1063108
user1063108

Reputation: 712

SQL Using subquery in where clause and use values in select

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

    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;

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

from #tdata m1
    left join (
        select amount1,amount2 from #tsubqry where cast(temestamp as date)<=cast(m1.temestamp as date)
    ) tt on m1.machine_id=tt.machine_id

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

Upvotes: 2

Views: 5977

Answers (2)

RoboBear
RoboBear

Reputation: 5764

MS SQL Server actually has a built-in programming construct that I think would be useful here, as an alternative solution to joining on a subquery:

-- # ###
-- # Legends
-- # ###
-- # 
-- # Table Name and PrimaryKey changes (IF machine_id is NOT the primary key in table 2, 
-- # suggest make one and keep machine_ie column as an index column).
-- #
-- #
-- #   #tdata   --> table_A
-- #   #tsubqry --> table_B
-- #

-- =====

-- SOLUTION 1 :: JOIN on Subquery

SELECT 
  m1.machine_id, 
  m1.commit_count, 
  m1.amount, 
  m2.amount1, 
  m2.amount2
FROM table_A m1
INNER JOIN (
    SELECT machine_id, amount1, amount2, time_stamp
    FROM table_B
) AS m2 ON m1.machine_id = m2.machine_id
WHERE m1.machine_id = m2.machine_id
  AND CAST(m2.time_stamp AS DATE) <= CAST(m1.time_stamp AS DATE);

-- SOLUTION 2 :: Use a CTE, which is specific temporary table in MS SQL Server 

WITH table_subqry AS
(
  SELECT machine_id, amount1, amount2, time_stamp
  FROM table_B
)
SELECT 
  m1.machine_id, 
  m1.commit_count, 
  m1.amount, 
  m2.amount1, 
  m2.amount2
FROM table_A m1
LEFT JOIN table_subqry AS m2 ON m1.machine_id = m2.machine_id
WHERE m1.machine_id = m2.machine_id
  AND CAST(m2.time_stamp AS DATE) <= CAST(m1.time_stamp AS DATE);

Also, I created an SQLFiddle in case it's helpful. I don't know what all your data looks like, but at least this fiddle has your schema and runs the CTE query qithout any errors. Let me know if you need any more help!

SQL Fiddle

Source: Compare Time SQL Server

SQL SERVER Using a CTE

Cheers.

Upvotes: 0

Obie
Obie

Reputation: 477

There seems to be several things wrong here but I think I see where you are trying to go with this.

The first thing I think you are missing is is the temestamp on the #tsubqry table. Since you are referencing it later I'm assuming it should be there. So, your table definition needs to include that field:

create table #tsubqry(
  machine_id varchar(12),
  amount1 decimal(6,2),
  amount2 decimal(6,2),
  temestamp datetime
);

Now, in your query I think you were trying to use some fields from #tdata in your suquery... Fine in a where clause, but not a from clause.

Also, I'm thinking you will not want to duplicate all the data from #tdata for each matching #tsubqry, so you probably want to group by. Based on these assumptions, I think your query needs to look something like this:

  select m1.machine_id, m1.commit_count, m1.amount, sum(tt.amount1), sum(tt.amount2)
  from #tdata m1
  left join  #tsubqry tt  on m1.machine_id=tt.machine_id
  and cast(tt.temestamp as date)<=cast(m1.temestamp as date)
  group by m1.machine_id, m1.commit_count, m1.amount

Upvotes: 2

Related Questions