Comparing two local database tables using C #

Question

I am new to C #.

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

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

There are two tables in my database. Nicknames and names. Nicknames have one field called "Nickname", which simply lists the nicknames. Names have one field called "Text", which simply lists the names stored.

Example:

Nickname

 Masher Jones Jaime John Joker 

Names

 John Adam Matt Jones 

Let's say these are the values ​​in my database. I want to find aliases that are not in the name table, which in my example are → Masher, Jaime and Joker.

How can i do this? I am using C # and VS2010.

Here is what I tried in terms of SQL codes:

  "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.

Answer 1

Try:

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

Edit: try instead:

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

My attempt @ is your code (ignore the fact that I use SQL syntax as what I'm working with):

Answer 2

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

should do exactly what you want.