Introduction
This article explains how to get the records in one table from one or more other tables. First we have explained about Joins in SQL Server and how to access the values from other tables. In this article we have explained only Inner Join, Left Join and Outer Join. Using these kinds of Joins we can access the records in one table from other tables.
Joins
SQL Joins play a very vital role in databases. Using joins we can retrieve values from other tables in a database. Records are accessed based on a relationship among the column fields among the tables.
The following are the types of joins described here:
- Inner Join
- Left Join
- Right Join
Inner Join
Inner Join acts like an intersection between the tables; it displays the matching records in the tables. It displays all the rows from the first table and displays matched rows from the second tables.
Syntax:
Select columnlists
from table1 t1
inner join table2 t2
on t1.fieldname=t2.fieldname
Left Join
Left Join will return all records in the left table (table-1) regardless if any of those records have a match in the right table (table-2).
Syntax:
Select columnlists
from table1 t1
left join table2 t2
on t1.fieldname=t2.fieldname
Right Join
Right Join will return all the records in the right table (table-2) regardless if any of those records have a match in the left table (table-1).
Syntax:
Select columnlists
from table1 t1
right join table2 t2
on t1.fieldname=t2.fieldname
Create Database joining
use joining
Create Table-1
create table table1(
UserId int primary key,UserName nvarchar(max),FirstName varchar(max),LastName varchar(max))
insert into table1 values(101,'Pankey','Pankaj','Lohani')
insert into table1 values(102,'Paru','Pravesh','Khanduri')
insert into table1 values(103,'Nicks','Nimit','Joshi')
insert into table1 values(104,'Ammu','Amit','Senwal')
insert into table1 values(105,'Ravi','Ravi','Kumar')
Create Table-2
create table table2(
EmpId int primary key identity (101,1),Address nvarchar(max),City varchar(max),DepartmentNo int,Salary money)
insert into table2(Address,City,DepartmentNo,Salary) values('A-43 strno-6 delhi','Delhi',1,10000)
insert into table2(Address,City,DepartmentNo,Salary) values('B-44 pratap vihar noida','Gr.Noida',2,20000)
insert into table2(Address,City,DepartmentNo,Salary) values('C-45 vinod nagar new delhi','New Delhi',3,30000)
insert into table2(Address,City,DepartmentNo,Salary) values('d-47 laxminagagr delhi','Delhi',4,40000)
insert into table2(Address,City,DepartmentNo,Salary) values('RK puram','Delhi',5,50000)
insert into table2(Address,City,DepartmentNo,Salary) values('mohammod pur','GR.NOIDA',6,10000)
insert into table2(Address,City,DepartmentNo,Salary) values('DLF CITY','Gurgaon',7,20000)
insert into table2(Address,City,DepartmentNo,Salary) values('ASHOK NAGAR','Noida',3,30000)
insert into table2(Address,City,DepartmentNo,Salary) values('Indirapuram','Noida',10,88000)
insert into table2(Address,City,DepartmentNo,Salary) values('Nehru Place','Delhi',11,60000)
Create Procedure for Inner Join
Create proc GetInnerJoin
as
begin
select t1.UserId,t1.FirstName,t1.LastName,t2.Address,t2.salary
from table1 t1 inner join table2 t2
on
t1.UserId=t2.EmpId
end
Create Procedure for Right Join
Create proc GetRightOuterJoin
as
begin
select t1.UserId,t1.FirstName,t1.LastName,t2.Address,t2.salary
from table1 t1 right join table2 t2
on
t1.UserId=t2.EmpId
end
Create Procedure for Left Join
Create proc GetLeftOuterJoin
as
begin
select t1.FirstName,t1.LastName,t2.DepartmentNo,t2.salary
from table1 t1 Left join table2 t2
on
t1.UserId=t2.EmpId
end
Now I want to show the retrieved data in a Windows Forms application just use the following steps.
Step 1:
Open Visual Studio then select "Create New Project" --> "F# Console Application".
Step 2:
Now go the Solution Explorer on the right side of Visual Studio. Right-click on "References" and select "Add references".
Step 3:
After selecting "Add References", in the Framwork template you need to select "System.Windows.Forms", "System.Drawing", "System.Xml" and "System.Data" while holding down the Ctrl key and click on "Ok."
Step 4:
Write the following code in the F# editor.
open System
open System.Xml
open System.Drawing
open System.Data.SqlClient
open System.Windows.Forms
open System.Data
let constring = @"Data Source=MCNDESKTOP38;Initial Catalog=Joining;User ID=sa; Password=Password$2"
let form = new Form()
form.Text <- "Joins"
let lbltbl1=new Label(Top = 10, Left = 0, Height = 20)
lbltbl1.Text<-"TABLE-1"
let adaptbl1 = new SqlDataAdapter("select * from table1",constring)
let ds = new DataSet()
adaptbl1.Fill(ds) |>ignore
//let form = new Form()
let gridview = new DataGridView(Top=30,Width=400, Height=200)
form.Controls.Add(gridview)
form.Controls.Add(lbltbl1)
gridview.DataSource <- ds.Tables.[0]
let lbltbl2=new Label(Top = 10, Left = 420, Height = 20)
lbltbl2.Text<-"TABLE-2"
let adaptbl2 = new SqlDataAdapter("select * from table2",constring)
let dt = new DataTable()
adaptbl2.Fill(dt) |>ignore
let gridview2 = new DataGridView(Top=30,Left=420,Width=510, Height=300)
form.Controls.Add(gridview2)
form.Controls.Add(lbltbl2)
gridview2.DataSource <- dt
let btn1 = new Button(Top = 350, Left = 20, Height = 20,Width=70)
btn1.Text <- "InnerJoin"
form.Controls.Add(btn1)
let con = new SqlConnection(constring)
//open connection
con.Open()
let com = new SqlCommand()
com.Connection <- con
com.CommandType <- CommandType.StoredProcedure
com.CommandText <- "GetInnerJoin"
btn1.Click.Add(fun _ ->
let adapter = new SqlDataAdapter("GetInnerJoin",con)
let ds = new DataSet()
adapter.Fill(ds) |>ignore
let gridview = new DataGridView(Top=450,Width=520,Height=180)
form.Controls.Add(gridview)
gridview.DataSource <- ds.Tables.[0]
)
//RightOuterjoin
let btn2 = new Button(Top = 380, Left =20, Height = 20, Width=70)
btn2.Text <- "RightJoin"
form.Controls.Add(btn2)
com.Connection <- con
com.CommandType <- CommandType.StoredProcedure
com.CommandText <- "GetRightOuterJoin"
btn2.Click.Add(fun _ ->
let adapter = new SqlDataAdapter("GetRightOuterJoin",con)
let dt = new DataTable()
adapter.Fill(dt) |>ignore
let gridview = new DataGridView(Top=450,Width=550,Height=270)
form.Controls.Add(gridview)
gridview.DataSource <- dt
)
//LeftOuterJoin
let btn3 = new Button(Top = 410, Left = 20, Height = 20, Width=70)
btn3.Text <- "LeftJoin"
form.Controls.Add(btn3)
com.Connection <- con
com.CommandType <- CommandType.StoredProcedure
com.CommandText <- "GetLeftOuterJoin"
btn3.Click.Add(fun _ ->
let adapter = new SqlDataAdapter("GetLeftOuterJoin",con)
let dt = new DataTable()
adapter.Fill(dt) |>ignore
let gridview = new DataGridView(Top=450,Width=450,Height=150)
form.Controls.Add(gridview)
gridview.DataSource <- dt
)
//Showing the form
Application.Run(form)
Step 5:
Debug the application by pressing F5 to execute the Windows Forms application. After debugging the application you will get a Windows Forms application as in the figure given below.
Step 6:
Click on "Inner Join".
Step 7:
Click on "Right Join".
Step 8:
Click on "Left Join".
Summary
In this article you saw how to retrieve the records from one or more other tables cooresponding to data in a table. We explained Right Join, Left Join, Inner Join and displayed all the records in a Windows Forms application.