Using 'In' with a Sub-Query in SQL Statements

SQL Using subquery in where clause and use values in select

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.

subquery in select statement vs subquery in FROM statement

SQL supports various types of subqueries. A very important distinction is between:

derived tables which are subqueries in the FROM clause that return a result set that can have multiple rows and multiple columns.
scalar subqueries which return (in general) one column and up to one row.

What you are describing as a "subquery in the SELECT clause is a scalar subquery. The value is a replacement for a literal value, so it can only return one column. If the scalar returns zero rows, then the value is NULL.

Note that scalar subqueries are sometimes extended to support multiple columns. In that case, the return value is really a tuple, so the value is still "one thing". But that thing can have multiple fields like a struct or record many programming languages.

Using LIKE and IN and a Subquery in a single SQL Statement

SELECT UserID, CASE WHEN EXISTS 
(
  SELECT 1 FROM dbo.Users WHERE UserPeers LIKE '%' + u.UserID + '%'
) THEN 'I am in Column1' ELSE UserID END
FROM dbo.Users AS u;

sql subquery from where in

You just need to use ta.fieldid instead of list_of_ids[i]

select ta.fielda,ta.fieldb,
       (select sum(tb.field1)-sum(tb.field2) as fieldc 
        from tableb tb
        where tb.fieldid = ta.fieldid ) 
from tablea  ta
where ta.fieldid in (list_of_ids);

Reuse of a field from a joined table inside a subquery in FROM clause

Recall SQL's logical order of operations that differ from its lexical order (i.e., order in how it is written). Usually the first step in query processing is the FROM clause, then JOIN, ON, WHERE, GROUP BY, etc. and usually ending with ORDER BY and SELECT (ironically one of the last clauses processed though written first).

Technically, your queries do not involve correlated subqueries since there are no inner or outer levels. Specifically, the derived table t3 and base table t4 are at the same level. The query engine evaluates t3 in isolation by itself during FROM clause step. Then, it evaluates JOIN table, t4, in isolation by itself and finally applies the matching ON logic.

Because t4 is not defined in the universe of t3, MS Access via GUI prompts for that parameter value (where MS Access via ODBC will raise an error). To resolve you have to include all necessary data sources in each table scope:

SELECT t1.field1, t1.field2 - IIF(t3.calcfield IS NULL, 0, t3.calc) As Diff
FROM
  (SELECT t2.fieldid, SUM(t2.field3) AS fsum
   FROM t2
   INNER JOIN table4 sub_t4
     ON t2.fieldid = sub_t4.fieldid
   WHERE t2.date > sub_t4.date 
   GROUP BY t2.fieldid
  ) t3
LEFT JOIN table4 t4 
  ON t3.fieldid = t4.fieldid

Often, too, using layered queries is beneficial in Access and can help with final, compact queries:

t3 query (save below as a query object)

SELECT t2.fieldid, SUM(t2.field3) AS fsum
FROM t2
INNER JOIN table4 sub_t4
   ON t2.fieldid = sub_t4.fieldid
WHERE t2.date > sub_t4.date 
GROUP BY t2.fieldid

Final query (join saved query)

SELECT t1.field1, t1.field2 - IIF(t3.calcfield IS NULL, 0, t3.calc) As Diff
FROM my_saved_query t3
LEFT JOIN table4 t4 
   ON t3.fieldid = t4.fieldid

Sql syntax: select without from clause as subquery in select (subselect)

This is the default behavior for the SQL language and it is defined on the SQL ANSI 2011 over ISO/IEC 9075-1:2011(en) documentation. Unfortunately it is not open. This behavior is described on the section 4.11 SQL-Statements.

This behavior happens because the databases process the select comand without the from clause, therefore if it encounters:

select id, (select name) from some

It will try to find that name field as a column of the outer queries to process.

Fortunately I remember that some while ago I've answered someone here and find a valid available link to an SQL ANSI document that is online in FULL but it is for the SQL ANSI 99 and the section may not be the same one as the new document. I think, did not check, that it is around the section 4.30. Take a look. And I really recommend the reading (I did that back in the day).

Database Language SQL - ISO/IEC 9075-2:1999 (E)

MS SQL subquery

Seems like you could use a subquery like the following. I assume that if Badang bought multiple items you would want the purchase to be greater than their highest value purchase:

SELECT C.Cust_Name,
       I.ItemName,
       I.Price
FROM dbo.Customers C
     JOIN dbo.Orders O ON  C.CustomerID = O.CustomerID
     JOIN dbo.Items I ON O.ItemID = I.ItemID
WHERE I.Price > (SELECT MAX(Isq.Price)
                 FROM dbo.Customers Csq
                      JOIN dbo.Orders Osq ON Csq.CustomerID = Osq.CustomerID
                      JOIN dbo.Items Isq ON Osq.ItemID = Isq.ItemID
                 WHERE Csq.Cust_Name = 'Badang');