PHP SQL Server SubSelect – SubQueries

A sub query or sub select is a select statement that returns a single value

output result and it can be nested inside other subquery or any SELECT, INSERT,
DELETE OR UPDATE statement.

Example:
USE NORTHWIND

SELECT P.PRODUCTNAME,
(SELECT CATEGORYNAME FROM CATEGORIES WHERE CATEGORYID = P.CATEGORYID)
FROM PRODUCTS P

Subquery used in the above example returns the category name of each product
in every tuple.

Example:

SELECT P.PRODUCTNAME, P.UNITPRICE, P.CATEGORYID
FROM
PRODUCTS P
WHERE
P.PRODUCTID = (SELECT PRODUCTID FROM PRODUCTS WHERE PRODUCTNAME=’VEGIE-SPREAD’)

Here subquery returns product id as single value to the main SQL query.

Example:

SELECT C.CATEGORYNAME,

(SELECT TOP 1 P.PRODUCTNAME FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID ORDER BY P.UNITPRICE DESC),

(SELECT TOP 1 P.UNITPRICE FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID ORDER BY P.UNITPRICE DESC),

(SELECT MAX(P.UNITPRICE) FROM PRODUCTS P WHERE P.CATEGORYID=C.CATEGORYID)
/*TO VERIFY THE RESULT*/

FROM CATEGORIES C

Above example returns the maximum unit price of product, product name and category name.