I am a newbie in 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:
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.
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.
How would i do that? 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?
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.
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):
Select [Alias] from [nicknames] where [Alias] not in (select [text] from [names])
should do exactly what you want.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With