Nested Select Statement in SQL Server

Nested select statement in SQL Server

You need to alias the subquery.

SELECT name FROM (SELECT name FROM agentinformation) a  

or to be more explicit

SELECT a.name FROM (SELECT name FROM agentinformation) a  

SQL Query Nested Select Syntax

Something like this (your desired output appears to be missing):

SELECT O.OfficeID
  , (
    SELECT COUNT(*)
    FROM AGENT A
    WHERE A.OfficeID = O.OfficeID
)
FROM OFFICE O
ORDER BY O.OfficeID

Note the use of the table alias which is a recommended practice to keep your queries concise.

Using a SELECT inside of another SELECT statement where the nested SELECT takes as input a value from the first SELECT

SELECT count(lskinid) AS "Total Subaccounts", 
(SELECT refname FROM lskin as [in] WHERE in.lskinid = out.masterlskin) AS "Account Name"
FROM lskin as [Out]
WHERE isactive = 1
Group by masterlskin
order by count(lskinid) DESC

SQL: nested select statement - child select reading var from main select statement

after playing around with the statement, that was the final result that worked for me:-

IF OBJECT_ID('tempdb..#temptbl', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE #temptbl

declare @totalreq int

select distinct p1.ProductName, p1.TotalQuantity, o1.OrderEventDate, (select SUM(od2.OrderDetailReqQuant)
                from OrderDetailTbl as od2
                inner join OrderTbl as o2
                    on od2.OrderId = o2.OrderId
                where o2.OrderEventDate = o1.OrderEventDate and od2.ProductId = 

                (select p3.ProductId from ProductTbl as p3 where p3.ProductName = p1.ProductName))  as TotalRequiredQuantity
into #temptbl
 from ProductTbl as p1
 inner join OrderDetailTbl as od1
    on p1.ProductID = od1.ProductId
 inner join OrderTbl as o1
    on o1.OrderId = od1.OrderId

    select * from #temptbl

all what I did is on each level of select statement I defined a unique name of reused table, so lets say in the main select statement I defined producttbl as p1 and in the first subquery I defined the producttbl again as p2 and in the second subquery I defined the producttbl as p3, so now I can compare values coming from the 3 levels of the statement and display result correctly. the results were as follows:-

ProductName    |TotalQuantity|OrderEventDate|TotalRequiredQuantity
__________________________________________________________________________
8' Tabel       | 50          | 2019-01-18   |14
Banquet Chairs |400          | 2019-01-17   |2
Banquet Chairs |400          | 2019-01-18   |12
White Chairs   |220          | 2019-01-18   |5 

Set table name in a SELECT statement from a nested select

in sql server you can use system partition table to get the row counts in a database :

SELECT * FROM
(SELECT
    QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],
    SUM(sPTN.Rows) AS [RowCount]
FROM
    sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id
WHERE
    sOBJ.type = 'U'
    AND sOBJ.is_ms_shipped = 0x0
    AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
    sOBJ.schema_id,
    sOBJ.name
ORDER BY [TableName]) X
WHERE X.[TableName] IN 
(SELECT 
CONCAT('[schema_name].[prefix', [TABLE_NAME], ']') 
FROM [db_name].[schema_name].[tables_list])

Outer SELECT takes the first result of the nested SELECT

At first glance, you might try the IN operator.

SELECT id, name FROM course AS c
WHERE c.id IN (
    SELECT cid FROM tran AS t1
    WHERE t1.id = (
        SELECT MAX(t2.id) FROM tran AS t2
        WHERE t1.nid = 111
        AND t1.cid = t2.cid
    )
    AND t1.state = "enrolled"
);

Haven't tested it myself, however.

---

Related Topics