PHP SQL Server SubSelect – SubQueries

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">