SQL Using subquery in where clause and use values in select

Question

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

Why dont you just use join for everything and not the subquery? — Mr Zach, Feb 03 '18 at 22:54
Agree. If all the tables have `machine_id` then join them all. — Jason, Feb 03 '18 at 23:04
Gentlemen I have edited code and tried with join but now I am getting The multi-part identifier "m1.temestamp" could not be bound error — user1063108, Feb 03 '18 at 23:27
Yes, your columns don't quite add up. You did a great job to try to include a working example we can just copy and paste!! It's a big help! But it doesn't have columns that all make sense. Where is temestamp in #tsubqry? But I agree with others that it's not clear you need a subquery here... why not another join? — Mike M, Feb 04 '18 at 01:16

score 1 · Answer 1 · answered Feb 04 '18 at 00:54

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

great points about the structure and when to reference parent fields..... but why the Group By? I guess the OP didn't say, but it seems quite possible the Machines have only one Amount1,Amount2. — Mike M, Feb 04 '18 at 01:20
Thanks Mike. Yeah, you may be right. I sort of inferred a one-to-many relationship between the two tables, but as I read it over again, there really is no reason it couldn't be one to one (or zero). if so, then the group by and sums wouldn't be needed. — Obie, Feb 04 '18 at 03:23

RoboBear · Accepted Answer · 2018-02-05T16:05:48.980

0

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.

edited Feb 05 '18 at 16:05

answered Feb 04 '18 at 00:46

RoboBear

5,434
2
33
40

Thanks, I have a follow up question, would you kindly let me know when a CTE is used as in your example, in the sequence of events for this query would you say CTE would be run first then the main query or there is another event sequence? – user1063108 Feb 04 '18 at 17:56
You would run the CTE as part of the main query, actually. Technically, it's a fancy way if making a temporary table such that the temporary table only exists for your query. The syntax for a CTE looks like: WITH [cteName] AS ( ... subquery ... ) [Main query that references CTE table right here] – RoboBear Feb 04 '18 at 19:12
Thanks, another followup question, in my case if CTE doesn't return data the whole query returns nothing. is there a way to say if(true) only then run cte end query with or without CTE? How can I run cte based upon some condition while main query run regardless? – user1063108 Feb 04 '18 at 20:34
Ah, in this case I think the best change to address that is to change the JOIN being used. INNER JOIN **only** returns rows that match on the condition in both tables. In this case I think you want a LEFT JOIN -- the main query should actually always return rows from the left part of the join (the first table, we call it m1). I'll update my code shortly to show this change. Let me know if this works out right. – RoboBear Feb 05 '18 at 16:05
in the where clause I am comparing timestamp from cte with main query, I changed the join from inner to left but still no results (looks like cus of date comparison) however when I comment out cte comparison in where clause then data is returned..now how to NOT compare dates in where clause when cte does not return any data? I tried few variations but doesn't seem to get the results, always no data – user1063108 Feb 05 '18 at 17:42
ok Thank you very much for your code example RoboBear as my problem is resolved using your code example as it gave me idea what to do. Again thanks a bunch – user1063108 Feb 06 '18 at 02:46
No problem! I'm glad it helped. – RoboBear Feb 06 '18 at 18:34

SQL Using subquery in where clause and use values in select

2 Answers2