Use subquery results in main query

use the results of a query as a field in another query
subquery column in main query
sql select * from results of another query
sql multiple subqueries in (select statement)
select from subquery
when to use subquery in sql
nested queries in sql w3schools
sql subquery join

How can I make subquery result available in the main query like this example?

SELECT Id AS ParticipantId, TeamId,
ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date), 0) AS FirstWeight,
ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date DESC), 0) AS LastWeight,
FirstWeight - LastWeight As WeightDiff // this doesn't work
FROM Participants

FirstWeight,LastWeight in line Alias will not work sql server you can change your query below way

SELECT Id AS ParticipantId, TeamId,
ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date), 0) AS FirstWeight,
ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date DESC), 0) AS LastWeight,
ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date), 0)- ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date DESC), 0) As WeightDiff // this doesn't work
FROM Participants

or you can use subquery to next level

select ParticipantId, TeamId,FirstWeight,LastWeight,
 FirstWeight-LastWeight as WeightDiff from
  ( 
  SELECT Id AS ParticipantId, TeamId,
    ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date), 0) AS FirstWeight,
    ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date DESC), 0) AS LastWeight
FROM Participants
  ) as t

Mysql Using Result of Subquery in Main Query, The problem is that using subqueries in the IN or SELECT clauses often causes them to be run again for each row in the outer query. To avoid� Determine if an expression is included in the results of the query. Check whether the query selects any rows. Syntax : The subquery (inner query) executes once before the main query (outer query) executes. The main query (outer query) use the subquery result. SQL Subqueries Example : In this section, you will learn the requirements of using

One option is to place your current query into a CTE, and then subquery it, making use of the aliases:

WITH cte AS (
    SELECT Id AS ParticipantId, TeamId,
        ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date), 0) AS FirstWeight,
        ISNULL((SELECT TOP(1) Weight FROM ParticipantData WHERE (ParticipantId = Participants.Id) AND (Weight <> 0) ORDER BY Date DESC), 0) AS LastWeight
    FROM Participants
)

SELECT
    ParticipantId,
    TeamId,
    FirstWeight,
    LastWeight,
    FirstWeight - LastWeight As WeightDiff
FROM cte;

SQL Subqueries, The main query (outer query) use the subquery result. SQL Subqueries Example : In this section, you will learn the requirements of using� Use a subquery as a field alias when you want to use the subquery results as a field in your main query. Note: A subquery that you use as a field alias cannot return more than one field. You can use a subquery field alias to display values that depend on other values in the current row, which is not possible without using a subquery.

Use outer apply:

SELECT Id AS ParticipantId, TeamId,
       COALESCE(f.FirstWeight, 0) as FirstWeight,
       COALESCE(f.LastWeight, 0) as LastWeight,
       (f.FirstWeight - l.LastWeight) As WeightDiff 
FROM Participants p OUTER APPLY
     (SELECT TOP(1) pd.Weight as firstWeight
      FROM ParticipantData pd
      WHERE (pd.ParticipantId = p.Id) AND Weight <> 0
      ORDER BY Date
     ) f OUTER APPLY
     (SELECT TOP(1) pd.Weight as lastWeight
      FROM ParticipantData pd
      WHERE (pd.ParticipantId = p.Id) AND Weight <> 0
      ORDER BY Date DESC
     ) l;

SQL Subquery, IN SELECT - with Examples, Subqueries are nested queries that provide data to the enclosing query. Subqueries can return individual values or a list of records; Subqueries must be enclosed� The advantage of using a subquery, in this case, is that it helps to make your queries more data-driven and less brittle. What I mean is you don’t have to hard code values. If for instance you’re doing a query to find sales order by top salespeople, the non-sub query way to use the IN statement is

How does a subquery use the main query columns?, Well, there are some misunderstandings about what the subquery is doing. First of all, EXISTS works in this case by evaluating the subquery for� A subquery, sometimes called a sub-SELECT, is a query whose results are used by another query. The main query uses the subquery’s result set as a comparison value for a selection criterion. You create a subquery when you need to compare a field value to the results of a second query.

Writing Subqueries in SQL, This lesson of the SQL tutorial for data analysis covers using subqueries in SQL with Here's an example of a basic subquery: Once the inner query runs, the outer query will run using the results from the inner query as its underlying table: The query that contains the subquery is called an outer query or an outer select. To execute the query, first, the database system has to execute the subquery and substitute the subquery between the parentheses with its result – a number of department id located at the location 1700 – and then executes the outer query.

SQL Server: Subqueries, This SQL Server tutorial explains how to use subqueries in SQL Server The subquery is then used to filter the results from the main query using the IN� You can see I used column aliases to help make the query results easier to read. I also used a table alias, SOD, for the outer query. This makes it possible to use the outer query’s values in the subquery. Otherwise, the query isn’t correlated! Using the table aliases make it unambiguous which columns are from each table.

Comments
  • Thanks. I knew that I could make the subqueries again to do the calculation, but I thought there were better approaches. Also with performance in mind.
  • @Mads cte or sub-query otherwise have to direct but in alias will not work
  • Could you help me complete your second example?
  • @Mads i edited that is just your query and make the difference in top query
  • Thanks, nice. I give you the correct answer due to performance.
  • That looks nice. Is it better in performance than multi subqueries?
  • No, the multiple subqueries version would probably perform better. But, it looks much worse, and might be harder to maintain from a coding point of view. Up to you as to which version you want to use.
  • @Mads I spent about 15 minutes trying to rewrite your query using regular joins, but was unable to do so. You might have to stick with your current approach, assuming you need this exact output.
  • You're right it looks much simpler than all the subqueries, but in this case I must have performance in mind.
  • Thanks, Gordon! How is CROSS APPLY in performance versus subqueries / joins?
  • @Mads . . . It should be equivalent to subqueries. You would have to test to see how it compares to JOINs in any particular case.