使用C＃比较两个本地数据库表

I am a newbie in C#.

我是C＃的新手。

I created a local database in VS2010(.sdf file). I am trying to create some comparisons between database columns. I successfully connected with the database using the connectionstring below:

我在VS2010（.sdf文件）中创建了一个本地数据库。我试图在数据库列之间创建一些比较。我使用下面的连接字符串成功连接数据库：

 string connectionString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;data source=c:\\users\\user\\documents\\visual studio 2010\\Projects\\myapp\\myapp\\mydb.sdf";

My database has two tables. Nicknames and Names. Nicknames has one field called "Alias" which simply lists the nicknames stored. Names has one field called "Text" which simply lists the names stored.

我的数据库有两个表。昵称和名字。 Nicknames有一个名为“Alias”的字段，它只列出存储的昵称。名称有一个名为“Text”的字段，它只列出存储的名称。

Ex:

例如：

Alias

别号

Masher
Jones
Jaime
John
Joker

Names

名称

John
Adam
Matt
Jones

Let's say these are the values in my database. What I want is to find the nicknames who aren't in the table of Names which is -> Masher, Jaime and Joker in my example.

假设这些是我数据库中的值。我想要的是在我的例子中找到不在姓名表中的昵称 - > Masher，Jaime和Joker。

How would i do that? I am using C# and VS2010.

我该怎么做？我正在使用C＃和VS2010。

Here is what i tried in terms of SQL codes:

这是我尝试的SQL代码：

 "SELECT Alias FROM nicknames WHERE (NOT (Alias IN(SELECT Text FROM Names))) ";
         "SELECT Alias FROM nicknames EXCEPT SELECT Text FROM Names";
         "SELECT Alias FROM nicknames t LEFT JOIN Names m ON m.Text = t.Alias WHERE m.Text IS NULL";
         "SELECT Alias FROM nicknames UNION SELECT Text FROM Names";

What should i do?

我该怎么办？

EDIT

 OleDbConnection conn = new OleDbConnection(connectionString);
        conn.Open();
 string sql = "MYQUERYFROMTHEABOVEEXAMPLE";
 OleDbCommand cmd = new OleDbCommand(sql, conn);
             OleDbDataReader reader;
             reader = cmd.ExecuteReader();
             string result = "";
             while (reader.Read())
            {
            result += reader.GetString(0) + "\n";
  }  

This is what i use to read the results.

这是我用来阅读结果的内容。

2 个解决方案

#1

Try:

尝试：

SELECT Alias FROM nicknames WHERE Alias not in (SELECT Text FROM Names)

Edit: Try this instead:

编辑：改为尝试：

select a.Alias
from nicknames a left outer join Names n on a.alias = n.text 
where n.text IS NULL

My attempt @ your code (Ignore the fact I use SQL syntax as that's what I work with):

我的尝试@你的代码（忽略我使用SQL语法的事实，就像我使用的那样）：

#2

Select [Alias] from [nicknames] where [Alias] not in (select [text] from [names])

should do exactly what you want.

应该做你想要的。