In this tutorial you will learn how to create a self-contained
multi-valued subquery. You may be wondering, what exactly is a
multi-valued subquery? It is a subquery that returns multiple values as a
single column, regardless of whether the subquery is self-contained. It
is used in a WHERE or HAVING expression that contains IN or a
comparison operator that is modified by ANY or ALL. The IN predicate
operates on a multi-valued subquery and the basic form looks like this:
<scalar_expression> IN (<multi-valued subquery>).
Setting Up
In this tutorial we will use two tables, Employees and Orders. Both tables have different columns but have a similar one in ‘EmpId’. This will serve as a way to connect the two tables when writing queries. Insert the values as shown in the screen shots below into the tables after creating them.
Employees – EmpId (int, not null), FirstName (nvarchar(30), not null), LastName (nvarchar(30), not null), HireDate (datetime, not null), MgrId (int, null), Ssn (nvarchar(20), not null), Salary (money, not null).
Orders – OrderId (PK, int, not null), EmpId (int, not null), CustId (nvarchar(30), not null), OrderTs (datetime, not null), Qty (int, not null).
If you’re ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
Multi-Valued Subquery
In this query we will select data from a table and filter it to find specific data. To do this we will use the WHERE clause twice, first to select EmpId from the Orders table and second to select LastName in the Employees table.
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server
Intellect specializes in providing complete internet-ready server
solutions backed by their expert 24/365 proactive support team.
As you can see from the code above, we use the typical SELECT, FROM, and WHERE statements/clause, but notice that we use them again after the IN operator. Remember that the IN operator allows you to specify multiple values in a WHERE clause thus allowing us to select multiple values in the LastName column.
Output
Since we specified to look for last names that begin with letters ‘D’ or ‘J’, the query returned orders of employees whose last name begin with ‘D’ or ‘J’. In this case we have two employees whose last name begin with those letters, John Johnson and Nathan Drake. Johnson has an EmpId of 1 and Drake has an EmpId of 6. If you look in the Orders table, we can see orders made by EmpId 1 and 6. The OrderId of those orders are 2, 3, 13, and 14, the same as what we got in the output.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
Download Scoure Code: Multi-Valued-Subquery.zip
Setting Up
In this tutorial we will use two tables, Employees and Orders. Both tables have different columns but have a similar one in ‘EmpId’. This will serve as a way to connect the two tables when writing queries. Insert the values as shown in the screen shots below into the tables after creating them.
Employees – EmpId (int, not null), FirstName (nvarchar(30), not null), LastName (nvarchar(30), not null), HireDate (datetime, not null), MgrId (int, null), Ssn (nvarchar(20), not null), Salary (money, not null).
Orders – OrderId (PK, int, not null), EmpId (int, not null), CustId (nvarchar(30), not null), OrderTs (datetime, not null), Qty (int, not null).
If you’re ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
Multi-Valued Subquery
In this query we will select data from a table and filter it to find specific data. To do this we will use the WHERE clause twice, first to select EmpId from the Orders table and second to select LastName in the Employees table.
Code Block
Multi-Valued-Subquery.sqlce
Create the Multi-Valued subquery.SELECT OrderId FROM Orders WHERE EmpId IN (SELECT E.EmpId FROM Employees AS E WHERE E.LastName LIKE N'D%' OR E.LastName LIKE N'J%');
As you can see from the code above, we use the typical SELECT, FROM, and WHERE statements/clause, but notice that we use them again after the IN operator. Remember that the IN operator allows you to specify multiple values in a WHERE clause thus allowing us to select multiple values in the LastName column.
Output
Since we specified to look for last names that begin with letters ‘D’ or ‘J’, the query returned orders of employees whose last name begin with ‘D’ or ‘J’. In this case we have two employees whose last name begin with those letters, John Johnson and Nathan Drake. Johnson has an EmpId of 1 and Drake has an EmpId of 6. If you look in the Orders table, we can see orders made by EmpId 1 and 6. The OrderId of those orders are 2, 3, 13, and 14, the same as what we got in the output.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
Download Scoure Code: Multi-Valued-Subquery.zip
Author: SQL ATOMS.Com
No comments:
Post a Comment