union two table and find duplicate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

union two table and find duplicate

Author

Topic

josef
Starting Member

15 Posts

Posted - 2011-04-23 : 22:30:43

can someone help me to find out the error in my sql statement
Thanks in advance



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\testdb.accdb;Persist Security Info=False")
        
Dim CmdStr As String = " SELECT id,name, amount FROM table1 UNION SELECT id,name,amount FROM table2 "
CmdStr = " SELECT name FROM table2 GROUP BY name HAVING ( COUNT(name) = 1 )"

       
        con.Open()
        Dim cmd As OleDbCommand = New OleDbCommand(CmdStr, con)
        cmd.ExecuteNonQuery()
        con.Close()
        MsgBox("Done")
    End Sub

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-24 : 06:15:44

Union implicitly does a distinct, hence removing any duplicates.

Try union all, or try intersect if you're on a version of SQL that has it.

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-24 : 07:56:37

In addition to the union all that Gail suggested, I noticed couple more things in your code.

First, the second assignment statement is overwriting the first one.
So you are really sending only the second statement to the server.
You may want to replace it with something like this.


Dim CmdStr As String = " SELECT name FROM (SELECT id,name, amount FROM table1 UNION ALL SELECT id,name,amount FROM table2)  GROUP BY name HAVING ( COUNT(name) > 1 ) "

This query will try to find names for which there are multiple rows in table1 and table2 combined.
If that is not exactly what you are looking for please post what the requirement is.
Also, I am writing the query using SQL server syntax, not sure if it will complain in access.

Second, cmd.ExecuteNonQuery, as the name suggests, does not return a record set.
You will get nothing at all back from the query.
If you do want to get a recordset back, use ExecutReader.
Sort of like this (my VB skills are pretty close to zero, I am writing C# without comments hoping that that is VB):


dim rdr = cmd.ExecuteReader
while rdr.Read()
   'Do what you want with the names, such as adding to a list box
   Console.WriteLine(rdr("name"))
end while

josef
Starting Member

15 Posts

Posted - 2011-04-26 : 01:00:00

Thanks all of you , i can get it to work now .Thank you again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources