Wednesday, 18 July 2012

Joins In Sql Server

JOINS
  • SQL JOINS are used to retrieve data from two or more tables and show that data in a single table on the basis of the JOIN condition.
  • A join is actually performed by the where clause which combines the specified rows of tables.
  • In SQL, tables are related to each other with keys(Primary key, Candidate key, Super key). Primary key is a column that contain unique value for each row
JOIN: TYPES
  • Inner join
    • Equi join
      • Natural join
      • Cross join
    • Non-equi join
  • Outer join
    • Left outer
    • Right outer
    • Full outer
  • Self join
  •  
  •  
  • INNER JOIN
    INNER JOIN will display all the records that have matched.
    Syntax:
    Select colname(s) from table_name1 inner join table_name2 using (colname)
    Reference Table 1: “student” table           * RollNo is the primary Key
    RollNo StudentName Marks
    1 abhi 78
    2 sunny 88
    3 Rajesh 300
    4 Rahul 400
    Reference Table 2: “project” table                                           * ProjId is the primary Key
    ProjID ProjName RollNo
    3 Java Beans 1
    5 Asp.Net 2
    8 Spring 3
    14 Hibernate 4
    Example:
    SQL> select StudentName, Marks, ProjName from student inner join project using(RollNo);
    Output:
    StudentName Marks ProjName
    abhi 78 Java Beans
    sunny 88 Asp.Net
    Rajesh 300 Spring
    Rahul 400 Hibernate

    INNER JOIN: TYPES
    EQUI JOIN
    EQUI JOIN is a join which contains an ‘=’ operator in the joins condition.
    Syntax:
    Select colname(s) from table_name1, table_name2 where table_name1.colname = table_name2. colname;
    Example:
    SQL> select StudentName, Marks, ProjName from student, project where student.RollNo= project. RollNo;
    Output:
    StudentName Marks ProjName
    abhi 78 Java Beans
    sunny 88 Asp.Net
    Rajesh 300 Spring
    Rahul 400 Hibernate

    NATURAL JOIN
    Natural join compares all the common columns.
    Syntax:
    Select colname(s) from table_name1 natural join table_name2;
    Example:
    SQL> select RollNo, StudentName, Marks, ProjName from student natural join project;
    Output:
    RollNo StudentName Marks ProjName
    1 abhi 78 Java Beans
    2 sunny 88 Asp.Net
    3 Rajesh 300 Spring
    4 Rahul 400 Hibernate

    CROSS JOIN
    CROSS JOIN will gives the cross/cartesion product.
    Syntax:
    Select colname(s) from table_name1 cross join table_name2;
    Example:
    SQL> select RollNo, StudentName, Marks, ProjName from student cross join project;
    Output:
    RollNo StudentName Marks ProjName
    1 abhi 78 Java Beans
    2 sunny 88 Java Beans
    3 Rajesh 300 Java Beans
    4 Rahul 400 Java Beans
    1 abhi 78 Asp.Net
    2 sunny 88 Asp.Net
    3 Rajesh 300 Asp.Net
    4 Rahul 400 Asp.Net
    1 abhi 78 Spring
    2 sunny 88 Spring
    3 Rajesh 300 Spring
    4 Rahul 400 Spring
    1 abhi 78 Hibernate
    2 sunny 88 Hibernate
    3 Rajesh 300 Hibernate
    4 Rahul 400 Hibernate

    NON EQUI JOIN:
    NON EQUI JOIN contains an operator other than ‘=’ in the joins condition.
    Syntax:
    Select colname(s) from table_name1, table_name2 where table_name1.colname> table_name2. colname;
    Example:
    SQL> select StudentName, Marks, ProjName from student, project where student.RollNo> project. RollNo;
    Output:
    StudentName Marks ProjName
    sunny 88 Java Beans
    Rajesh 300 Asp.Net
    Rahul 400 Spring
     
  • OUTER JOIN
    Outer join gives the non-matching records along with matching records.
    OUTER JOIN: TYPES
    LEFT OUTER JOIN
    LEFT OUTER JOIN will display the all matching records and the records which are in left hand side table those that are not in right hand side table.
    Syntax:
    Select colname(s) from table_name1 left outer join table_name2 on table_name1.colname= table_name.colname;
    Reference Table 1: “student” table                                      * RollNo is the primary Key
    RollNo StudentName Marks
    1 abhi 78
    2 sunny 88
    3 Rajesh 300
    4 Rahul 400


    Reference Table 2: “project” table                                        * ProjId is the primary Key
    ProjID ProjName RollNo
    3 Java Beans 1
    5 Asp.Net 2
    8 Spring 3

    Example:
    SQL> select StudentName, Marks, ProjName from student left outer join project on student.RollNo= project. RollNo;
    Output:
    RollNo StudentName Marks ProjName
    1 abhi 78 Java Beans
    2 sunny 88 Asp.Net
    3 Rajesh 300 Spring
    4 Rahul 400

    RIGHT OUTER JOIN
    RIGHT OUTER JOIN will display the all matching records and the records which are in right hand side table those that are not in left hand side table.
    Syntax:
    Select colname(s) from table_name1 right outer join table_name2 on table_name1.colname= table_name.colname;
    Example:
    SQL> select StudentName, Marks, ProjName from student right outer join project on student.RollNo= project. RollNo;
    Output:
    RollNo StudentName Marks ProjName
    1 abhi 78 Java Beans
    2 sunny 88 Asp.Net
    3 Rajesh 300 Spring



    		Hibernate
    SQL: FULL OUTER JOIN
    FULL OUTER JOIN will display the all matching records and the non-matching records from both tables.
    Syntax:
    Select colname(s) from table_name1 full outer join table_name2 on table_name1.colname= table_name.colname;
    Example:
    SQL> select StudentName, Marks, ProjName from student full outer join project on student.RollNo= project. RollNo;
    Output:
    RollNo StudentName Marks ProjName
    1 abhi 78 Java Beans
    2 sunny 88 Asp.Net
    3 Rajesh 300 Spring
    4 Rahul 400



    		Hibernate
     
  • SELF JOIN
    Joining the table itself is called self join.
    Syntax:
    Select colname(s) from table_name t1, table_name t2 where t1.colname=t2.colname;
    Reference Table 1: “student” table                                      * RollNo is the primary Key
    RollNo StudentName ProjID Marks
    1 abhi 2 78
    2 sunny 1 88
    3 Rajesh 4 300
    4 Rahul 3 400

    Example:
    SQL> select t1.StudentName, t2.Marks from student t1, student t2 where t1.RollNo=t2.ProjId;
    Output:
    StudentName Marks
    abhi 88
    sunny 78
    Rajesh 400
    Rahul 300
     
Posted by at No comments:

Saturday, 7 July 2012

How to find your search location using VB.NET



Here I am going to discuss a simple application to find your search location in the google map, by street, city, state, zip code wise. You can also search your location by Latitude and Longitude.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table width="300px" cellpadding="2" cellspacing="2" style="border: 1px solid maroon;">
                <tr>
                    <td colspan="2">
                        &nbsp;<b>Search by Street, City, State and ZipCode</b>
                    </td>
                </tr>
                <tr>
                    <td>
                        Street
                    </td>
                    <td>
                        <asp:TextBox ID="txtStreet" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        City
                    </td>
                    <td>
                        <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        State
                    </td>
                    <td>
                        <asp:TextBox ID="txtState" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        ZipCode
                    </td>
                    <td>
                        <asp:TextBox ID="txtZipCode" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    </td>
                    <td>
                        <asp:Button ID="ButtonSearch" runat="server" Text="Search" OnClick="ButtonSearch_Click" /><br />
                    </td>
                </tr>
            </table>
        <div>&nbsp;
        </div>
            <table width="300px" cellpadding="2" cellspacing="2" style="border: 1px solid maroon;">
                <tr>
                    <td colspan="2">
                        &nbsp;<b>Search by Latitude and Longitude</b>
                    </td>
                </tr>
                <tr>
                    <td>
                        Latitude</td>
                    <td>
                        <asp:TextBox ID="txtLat" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                        Longitude</td>
                    <td>
                        <asp:TextBox ID="txtLong" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    </td>
                    <td>
                        <asp:Button ID="ButtonLatLong" runat="server" Text="Search by Lat Long" OnClick="ButtonLatLong_Click" CausesValidation="false" />
                    </td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>

Add reference as follows:



Figure 1:




Figure 2:

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Text
Imports System.Drawing
Imports System.Windows.Forms

Partial Public Class _Default
    Inherits System.Web.UI.Page
    Private url As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    End Sub

    Protected Sub ButtonSearch_Click(ByVal sender As Object, ByVal e As EventArgs)
        Try
            Dim street As String = String.Empty
            Dim city As String = String.Empty
            Dim state As String = String.Empty
            Dim zip As String = String.Empty

            Dim queryAddress As New StringBuilder()
            queryAddress.Append("http://maps.google.com/maps?q=")

            If txtStreet.Text <> String.Empty Then
                street = txtStreet.Text.Replace(" "c, "+"c)
                queryAddress.Append(street + ","c + "+"c)
            End If

            If txtCity.Text <> String.Empty Then
                city = txtCity.Text.Replace(" "c, "+"c)
                queryAddress.Append(city + ","c + "+"c)
            End If

            If txtState.Text <> String.Empty Then
                state = txtState.Text.Replace(" "c, "+"c)
                queryAddress.Append(state + ","c + "+"c)
            End If

            If txtZipCode.Text <> String.Empty Then
                zip = txtZipCode.Text.ToString()
                queryAddress.Append(zip)
            End If

            url = queryAddress.ToString()
            Response.Redirect(url, False)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString(), "Unable to Retrieve Map")
        End Try

    End Sub

    Protected Sub ButtonLatLong_Click(ByVal sender As Object, ByVal e As EventArgs)
        If txtLat.Text = String.Empty OrElse txtLong.Text = String.Empty Then
            MessageBox.Show("Supply a latitude and longitude value", "Missing Data")
            Exit Sub
        End If

        Try
            Dim lat As String = String.Empty
            Dim lon As String = String.Empty

            Dim queryAddress As New StringBuilder()
            queryAddress.Append("http://maps.google.com/maps?q=")

            If txtLat.Text <> String.Empty Then
                lat = txtLat.Text
                queryAddress.Append(lat & "%2C")
            End If

            If txtLong.Text <> String.Empty Then
                lon = txtLong.Text
                queryAddress.Append(lon)
            End If

            url = queryAddress.ToString()
            Response.Redirect(url, False)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString(), "Error")
        End Try
    End Sub
End Class

Output:




Posted by at No comments:
Subscribe to: Posts (Atom)