Home and Learn: Intermediate Programming
SQL Queries, Joins, Stored Procedures
You've used SQL queries in previous lessons. You selected all the record from a single table with something like this:
SELECT * FROM Students
You can try that again. In the Server Explorer window, right click on the Tables folder and Select New Query from the menu:
In the Query window enter the above SQL. You query window should look like this:
(SQL Commands are not case-sensitive, by the way. You could enter the SELECT and the FROM in lowercase, if you wanted to.)
Click on the Green Arrow in the top left to run the query. You should see all the records from the Students table displayed. Now change the Students to Course. Run the query again and you'll see all the records from the Course table displayed.
You don't have to select all the records. You can specify the columns you want by typing the table name first, then a dot. You should see a list of available columns in your table:
Select the column you want in your results. To add more columns, separate them with commas:
SELECT Students.GivenName, Students.FamilyName FROM Students
However, what we want to do is to display results from both tables. To do that, we need something called a JOIN.
Sql Server Database JOIN
If you wanted to display only certain selected columns from both tables, you could do it like this:
SELECT Students.GivenName, Students.FamilyName,
Course.Course
FROM Students, Course
WHERE Students.StudentID = Course.StudentID
Here, we're selecting only three columns FROM both tables. Notice that the table names are separated by commas. (The dbo part is the schema that SQL Server gives us by default.) The WHERE clause at the end tells SQL Server to only display the records where the StudentID columns match in both tables.
In fact, try that out. Open a New Query and type the above into the window. Run your query to see the results.
There is another way to do this, though: use a JOIN.
There are a few different types of JOIN (inner, outer, cross, left, right, full) but they all work with a Primary Key in one Table and a Foreign Key in another. The difference is in how many results you want back, and whether to include NULL records or not.
The one we'll use is an INNER JOIN. The format is this:
SELECT [Columns]
FROM [Parent Table]
INNER JOIN [CHILD TABLE]
ON [Primary Key = Foreign Key]
The SQL Keywords are SELECT, FROM, INNER JOIN, ON. The parent table is the one with the StudentID Primary Key. The child table is the one with the StudentID as the Foreign Key. The INNER JOIN is ON the two.
To try it out, create a new query. Now enter the following: (You don't have to have the SQL on separate lines, but it does make it more readable.)
SELECT Students.GivenName, Students.FamilyName,
Course.Course
FROM Students
INNER JOIN Course
ON Students.StudentID = Course.StudentID
Run your query and you'll see the results appear:
You can actually miss out the INNER part at the start. So just have JOIN instead of INNER JOIN. The result is the same.
The point about learning all this SQL is that you can use it in your programming code to pull records from your database. To make your life easier as a programmer, and to help with security issues, you can create something called a Stored Procedure in your database. You can then grab your Stored Procedures with code. Let's create one.
Stored Procedures
To create a Stored Procedure, right-click the Stored Procedure folder in the Server Explorer. From the menu select Add New Stored Procedure:
A new window will appear with some complex-looking SQL. This:
The CREATE line is where you can change the name of your procedure. Click into the square brackets and delete the Word Procedure. Type a new name. Type NamesAndCourses:
CREATE PROCEDURE [dbo].[ NamesAndCourses]
We'll get onto parameters shortly. For now, delete the two lines that start with @param. Leave the As keyword. In place of the SELECT line, copy and paste your Join SQL. You should now have this:
Click the Update button and you'll see the Preview box appear:
Click Update Database.
In the Server Explorer on the left click the Refresh button:
You should now see your new Stored Procedure in the folder:
When we get to the coding section, we'll grab this Stored Procedure using its name, NamesAndCourses.
You can run this Stored Procedure. Right click it and select Execute from the menu:
You should the results of the query displayed in the bottom window and some SQL at the top. The table of results is what we're after. This is what we'll get when execute our query with code later.
Now that we have a Stored Procedure that gets all the results, let's create one to limit the results we get back. This time, we'll use those one of the @param values you deleted earlier. We'll do that in the next lesson below.
Back to the Intermediate Programming Contents Page