How To Compared Data Between Two Table With Same Database

[nimz143]

Hi.

Im using VS2008 and SQL Server Management Studio 2008.

I want to ask, i have two different table, TableA and TableB in same database, DatabaseX.
Both table have two same column, ColumnY but the data are different within this table.

Example:

TableA, ColumnY : A1, B2, C3, D4
TableB, ColumnY : A1, B2, C3, D4, E5, F6, G7

I want to compared data between this two table in my program. How the SQL Statement look alike because I dont know how to compared between this to?

And one more thing, can I just directly write the SQL statement in aspx.vb page?
or I need to write the SQL Statement in .vb page then called the function in aspx.vb page?

My code for my aspx.vb page

\

Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim i As Integer
        Dim iResult As Integer
        Dim hypWayID As HyperLink
        Dim chkRemove As New CheckBox
        Dim clsWay As New Way()

        Try

            For i = 0 To grdWay.Rows.Count - 1
                chkRemove = DirectCast(grdWay.Rows(i).FindControl("chkRemove"), CheckBox)
                hypRouteID = DirectCast(grdWay.Rows(i).FindControl("hypWayID"), HyperLink)

                If chkRemove.Checked Then



                    ///to check condition either the way ID exist in database
                    ///if not exist then way ID can be delete
                    ///if exist, message box appear show message this cannot be delete



                    iResult = clsWay.DeleteRoute(hypWayID.Text)
                End If
            Next i
            labelError.Text = "Item(s) delete successfully."
            BindGrid("%", "%", drpArea.SelectedValue,drpType.SelectedValue)
        Catch ex As Exception
            lblError.Text = ex.Message
        End Try

    End Sub

Thank you :)

[djjeavons]

Hi

If you want to return data from both tables where the contents of ColumnY are the same, then you can use the following query that simply does an INNER JOIN on the two tables:

SELECT        dbo.TableA.ColumnY
FROM            dbo.TableA INNER JOIN
                         dbo.TableB ON dbo.TableA.ColumnY = dbo.TableB.ColumnY

If you only want to return data from TableB where it does not exist in TableA then you can use a FULL OUTER JOIN with a Null criteria check:

SELECT        dbo.TableA.ColumnY, dbo.TableB.ColumnY AS TableBColumnY
FROM            dbo.TableA FULL OUTER JOIN
                         dbo.TableB ON dbo.TableA.ColumnY = dbo.TableB.ColumnY
WHERE        (dbo.TableA.ColumnY IS NULL)

And one more thing, can I just directly write the SQL statement in aspx.vb page?

It really depends on how you are architecting your solution. There are many ways in ASP.NET to communicate with a database; from standard drag and drop wizards which add various controls to your aspx page for specifying connections, select statements and display, to adding the code in your code behind file or in another library in your solution, such as a Data Access Layer.

Assuming that you are quite new to this (please correct me if I am wrong), then a simple approach would be to add it to your code behind file. However, I stress that if this is going to be a large application then I would thoroughly recommend looking at architecting it correctly from the beginning to save you a number of headaches later on.

The following is a quick example of how you might execute the above SQL statement in the code behind file and bind the results to a GridView:

'Connection string and SQL statement to run. The SqlDataAdapter will use these two pieces to connect and grab the data
Dim connectionString As String = "Server=YourServer;Database=DatabaseX;Trusted_Connection=True"
Dim selectStatement As String = "SELECT dbo.TableA.ColumnY, dbo.TableB.ColumnY AS TableBColumnY FROM dbo.TableA " _
                                  & "FULL OUTER JOIN dbo.TableB ON dbo.TableA.ColumnY = dbo.TableB.ColumnY WHERE" _
                                  & " (dbo.TableA.ColumnY IS NULL)"

Using adapter As New SqlDataAdapter(selectStatement, connectionString)

    Dim table As New DataTable

    'Fill the data table and bind to the GridView
    adapter.Fill(table)

    GridView1.DataSource = table
    GridView1.DataBind()

End Using

HTH

[nimz143]

Djjeavons, I have a question.

Can I use this SQL statement for create stored procedure?

SELECT        dbo.TableA.ColumnY, dbo.TableB.ColumnY AS TableBColumnY
FROM          dbo.TableA FULL OUTER JOIN 
              dbo.TableB ON dbo.TableA.ColumnY = dbo.TableB.ColumnY
WHERE        (dbo.TableA.ColumnY IS NULL)

[djjeavons]

Yes, you could put this into a stored procedure and then call the stored procedure from your code.

The code would only need to be modified slightly to indicate that you are running a stored procedure:

Using adapter As New SqlDataAdapter("yourStoredProcedure", connectionString) 'Modify accordingly

    adapter.SelectCommand.CommandType = CommandType.StoredProcedure

    Dim table As New DataTable

    'Fill the data table and bind to the GridView
    adapter.Fill(table)

    GridView1.DataSource = table
    GridView1.DataBind()

End Using

[hari sindam]

thanks
http://adminsupports.com

[nimz143]

Ddjjeavons, it's my stored procedured being writing right?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Del_Way_ID] @M_RTE_P varchar(10) 

AS
BEGIN
    SELECT [dbo].[ODS_PDS_TRWAY].[M_RTE_P], [dbo].[ODS_PDS_KQWAY].[M_RTE_P] 

    AS [ODS_PDS_KQWAY].[M_RTE_P] 

    FROM [dbo].[ODS_PDS_TRWAY] 

    FULL OUTER JOIN [dbo].[ODS_PDS_KQWAY] ON

    [dbo].[ODS_PDS_TRWAY].[M_RTE_P] = [dbo].[ODS_PDS_KQWAY]

    WHERE ([dbo].[ODS_PDS_TRWAY].[M_RTE_P] IS NULL)


SET NOCOUNT ON;

END
GO

[djjeavons]

Hi

What is the purpose of the @M_RTE_P parameter? I don't see it being used in your query. If you try to run this from code as it stands you will get an exception stating that the stored procedure expects a parameter. Other than that, it looks ok.

[nimz143]

I had remove this parameter @M_RTE_P and try to execute this SP but it had an error.

Error display is

Msg 102, Level 15, State 1, Procedure sp_Del_Way_ID, Line 10 Incorrect syntax near '.'.

How to solve this?

[djjeavons]

[dbo].[ODS_PDS_KQWAY] I am assuming is a table, but in the join section you are not stating what [db].[ODS_PDS_TRWAY].[M_RTE_P] should be joined on, you are only stating that it is joined to [dbo].[ODS_PDS_KQWAY]. This is possibly the cause of the error.

If that doesn't help, can you provide the structure of your two tables and some test data (maybe a create script would be useful).

[nimz143]

Hi djjeavons.

May I know, instead of using full outer join, its there any other method?

I'm trying to figure out another method, it is correct?

CREATE PROCEDURE [dbo].[sp_Del_Way_ID] @M_RTE_P varchar(10)

AS
BEGIN
    SELECT M_RTE_P FROM [dbo].[ODS_PDS_KQWAY]
    WHERE M_RTE_P  NOT IN (SELECT M_RTE_P FROM [dbo].[ODS_PDS_TRWAY])
    and M_RTE_P = @M_RTE_P
END

[nimz143]

Ouh ya, here my screenshot of program that i would like to do :

[djjeavons]

Hi

I'm not sure I fully understand the problem. Currently your SELECT statement will return all records from ODS_PDS_KQWAY where it is NOT in ODS_PDS_TRWAY. What do you want to do with that data? Do you actually want to delete it from ODS_PDS_KQWAY? So that would be deleting A14-A17 from ODS_PDS_KQWAY? If so, the following statement would work:

DELETE FROM [dbo].[ODS_PDS_KQWAY]
    WHERE M_RTE_P  NOT IN (SELECT M_RTE_P FROM [dbo].[ODS_PDS_TRWAY])

I note however in your stored procedure that you have a parameter. What is this used for? Are you trying to only delete one value if it does not exist in ODS_PDS_TRWAY? If that is the case then again, the following statement should work:

DELETE  FROM [dbo].[ODS_PDS_KQWAY]
    WHERE M_RTE_P  NOT IN (SELECT M_RTE_P FROM [dbo].[ODS_PDS_TRWAY])
    and M_RTE_P = @M_RTE_P

In regards to the private message you sent me, unfortunately, that did not make it much clearer as you have changed the schema, but I think the principle is the same.

HTH

[nimz143]

Hi djjeavons :)

Thanks a lotttttttttt ! You help me to figured out this SP code. Now, i'm able to use it and my program working !! =)

Thanks! Your guide help me to solve this error and make my program work =)

Thank you !