SQL Server Joins for Newbies

I have used the following 2 tables Employee and Department as examples.


Employee Table :- Department Table:-

EmployeeID EmployeeName DepartmentID DepartmentID DepartmentName
1 Smith 1 1 HR
2 Jack 2 2 Finance
3 Jones 2 3 Security
4 Andrews 3 4 Sports
5 Dave 5 5 HouseKeeping
6 Jospeh 6 Electrical
************************************************************************************************
Inner Join

An Inner Join will take two tables and join them together based on the values in common columns ( linking field ) from each table.

Example 1 :- To retrieve only the information about those employees who are assigned to a department.

Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee Inner Join Department on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

EmployeeID EmployeeName DepartmentName
1 Smith HR
2 Jack Finance
3 Jones Finance
4 Andrews Security
5 Dave HouseKeeping

Example 2:- Retrieve only the information about departments to which atleast one employee is assigned.

Select Department.DepartmentID,Department.DepartmentName From Department Inner Join Employee on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

DepartmentID DepartmentName
1 HR
2 Finance
3 Security
5 HouseKeeping
************************************************************************************************

Outer Joins :-

Outer joins can be a left, a right, or full outer join.

Left outer join selects all the rows from the left table specified in the LEFT OUTER JOIN clause, not just the ones in which the joined columns match.

Example 1:- To retrieve the information of all the employees along with their Department Name if they are assigned to any department.


Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee LEFT OUTER JOIN Department on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

EmployeeID EmployeeName DepartmentName
1 Smith HR
2 Jack Finance
3 Jones Finance
4 Andrews Security
5 Dave HouseKeeping
6 Jospeh

Right outer join selects all the rows from the right table specified in the RIGHT OUTER JOIN clause, not just the ones in which the joined columns match.


Example 2:- use Right Outer join to retrieve the information of all the departments along with the detail of EmployeeName belonging to each Department, if any is available.

Select Department.DepartmentID,Department.DepartmentName,Employee.EmployeeName From Employee Outer Join Department on Employee.DepartmentID = Department.DepartmentID

The ResultSet will be :-

DepartmentID DepartmentName EmployeeName
1 HR Smith
2 Finance Jack
2 Finance Jones
3 Security Andrews
4 Sports NULL
5 HouseKeeping Dave
6 Electrical NULL

This query will result in Null value for Employee Name where no Employee is assigned to that department.

Remove View State in .NET

On the page with by using:

  Page.EnableViewState = false;

Disabled it in the @page directives:

<%@ Page language="c#" Codebehind="odds.aspx.cs" AutoEventWireup="false" Inherits="pokerweb.help.odds" EnableViewState="false" EnableSessionState="False"%>

...disabled it in the web.config with

...and although small, the viewstate is still there. The only way i've found to completely remove it is to overide the Render method of the page and manually strip the form field out. this seems to work

using System.IO;

protected override void Render( System.Web.UI.HtmlTextWriter writer )

{

// Obtain the HTML rendered by the instance.
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter( sw );
base.Render( hw );
string html = sw.ToString();

hw.Close();
sw.Close();

// Find the viewstate.
int start = html.IndexOf( @" -1 )
{
int end = html.IndexOf( "/>", start ) + 2;

// Strip out the viewstate.
string viewstate = html.Substring( start, end - start );
html = html.Remove( start, end - start );

}

// Send the results back into the writer provided.
writer.Write( html );
}

Binding DropDown from XML data Files

Let us define the xml file first. This consists of Country object with properties ID and Name.




1
Nepal


2
India

3
China


4
Bhutan


5
USA




Select from the list of countries:


Now in the load event of the page, we read the items from the xml file into a dataset. We will get the DataView for the default table in the dataset, and sort it. The table will consists of as many rows as there are country objects in the xml file. Each row has two columns: ID and Name. Now we can bind this dataview to the dropdownlist control, as in the code (in C#) below.
view plainprint?

//page load event handler
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//call to the function to populate dropdownlist from xml
PopulateDDLFromXMLFile();
}
}

//populates the dropdownlist from xml file
public void PopulateDDLFromXMLFile()
{
DataSet ds = new DataSet();
ds.ReadXml(MapPath("~/Resources/XMLFile.xml"));

//get the dataview of table "Country", which is default table name
DataView dv = ds.Tables["Country"].DefaultView;
//or we can use:
//DataView dv = ds.Tables[0].DefaultView;

//Now sort the DataView vy column name "Name"
dv.Sort = "Name";

//now define datatext field and datavalue field of dropdownlist
ddlCountry.DataTextField = "Name";
ddlCountry.DataValueField = "ID";

//now bind the dropdownlist to the dataview
ddlCountry.DataSource = dv;
ddlCountry.DataBind();
}

That simple!
Good Luck!