Sunday, December 2, 2012

Report Building Part 3: Lets all JOIN hands

This next segment is dedicated to JOINs and how they are basically the backbone of querying the SCCM 2012 Database.  If you haven't already done so please download and review the SCCM 2007 Schema located here.

As mentioned before, the SCCM database is pretty big.  When I first started out with SCCM 2007 I didn't have a full appreciation for how much information is actually stored in this database.  Consider that it collects information on all the installed applications, hardware, collection memberships, deployment packages, Application Packages, OS Deployment, PXE, etc ... that is a lot of data to store.

So, lets get right to it.

JOIN

A JOIN allows you to combine information from two or more related tables.  A JOIN can be one-to-one, one-to-many, or many-to-many.  As you add JOINs to your query you increase the amount of information that the query will return.  There are four main types of JOINs: INNER, OUTER, LEFT, and RIGHT.  You can also do a self-join but that is out of the scope of this series.  In the book Microsoft SQL Server 2008 Reporting Services (ISBN-13:978-0071548083) , the Author describes JOINs best by suggesting that you consider two overlapping circles, each representing a database table.  The part in the center where they overlap (data that is the same in both tables) is an INNER JOIN.  The data outside the center portion on the left is an LEFT OUTER JOIN and the data outside the center portion on the right is a RIGHT OUTER JOIN. 

INNER JOIN

An INNER JOIN is probably the most common type of JOIN that you will use with basic SQL Queries.  Basically an INNER JOIN compares the selected fields from two tables and merges the results into your Report.  NULL values (blank fields) will not be included in an INNER JOIN unless you specify a join condition like IS NULL or IS NOT NULL.  With basic SCCM Queries you will not have to worry about NULL values I think.  Here are a few different ways you can do INNER JOINs:

1: Explicit JOIN notation
SELECT *
FROM dbo.employee
INNER JOIN dbo.department ON dbo.employee.DepartmentID = dbo.department.DepartmentID;

2: Implicit JOIN notation
SELECT *
FROM dbo.employee, dbo.department
WHERE dbo.employee.DepartmentID = dbo.department.DepartmentID;

OUTER JOIN

For more advanced SQL Queries you will see mostly LEFT OUTER or RIGHT OUTER joins.  The LEFT and RIGHT refer to which side of the = sign takes preference.  In a LEFT OUTER join for instance, all the records from the left side of the = sign will be in the report even if there is NULL values on the right.  For Example:

SELECT *
FROM dbo.employee
LEFT OUTER JOIN dbo.department ON dbo.employee.DepartmentID = dbo.department.DepartmentID;

This will list all employees regardless of if they have been assigned to a department.  The reverse of that, a RIGHT OUTER JOIN, would list all departments regardless of what employees are assigned.

Hopefully this will give you more of an understanding on JOINs and how they affect your SQL Query.  Next we will dive into Parameters, the difference between Dataset and Report Parameters and when to use them ... just as soon as I learn how to use them!

Stay Tuned!

Table of Contents

Introduction to Series
Part 1: The Goal
Part 2: SQL Query Basics
Part 3: Lets all JOIN hands

No comments: