[SOLVED] sqlite select query with foreign key for another table

sqlite select query with foreign key for another table

I am working on a project with xamarin android using the sqlite.net library. I have a select query that will execute and create a collection of custom objects called worker :

var command = conn.CreateCommand("SELECT * FROM tblWorkers");
var results = command.ExecuteQuery<Worker>();
ObservableCollection<Worker> workers = new ObservableCollection<Worker>(results);
return workers;

One of the columns is a foreign key and I need to get a value from that table just wondering what the best way to do that is. The foreign key on the data table tblWorkers is TitleID On that table is a varchar(datatable : tblTitles column : Title - nvarchar) I need to retrieve just wondering what the best way to do that is?

Solution

var command = conn.CreateCommand("SELECT * FROM tblWorkers LEFT JOIN tblTitles ON tblWorkers.TitleID = tblTitles.id");

Now, the above will work, but in general you'll want to avoid SELECT * usage. Only get the fields you want. Ideally, something like this...

var command = conn.CreateCommand("SELECT tblWorkers.SomeFieldYouWant, tblWorkers.SomeOtherFieldYouWant, ... , tblTitles.Title FROM tblWorkers LEFT JOIN tblTitles ON tblWorkers.TitleID = tblTitles.id");