Getting an Inner Join to work

[André_van_Haren]

For weeks I am trying to get an inner Join between 2 tables to work. One table is called Stories and contains story information and the other table is called Characters and contains character information, this table has a field the refers to the ID field in the Stories table. The goal is after selecting a Story title in the Stories listbox, only see the Characters that belong to that story in the Characters Listbox.

I studied the info in the Database basics for beginners — Xojo documentation page and came up with this code that works in the “DB Browser for Sqlite” Execute Sqlite tab:

SELECT * FROM characters INNER JOIN Stories on Stories.ID = Characters.FKStory WHERE stories.Story_title = 'A Christmas Carol'

The results are correct, only the characters belonging to the story title that I added at the end (‘A Christmas Carol’) are shown.

I cannot get this in Xojo to work though. I placed this code in the ListBoxCharacters. Running the app shows me ALL the characters from ALL the stories in the database, not only from '‘A Christmas Carol’.

Of course that last part should be changed; I don’t want to add the Story title in the code, but select a story from the StoriesListbox instead. But I figured I want to get this part to work first.

So why is this code working correctly in the DB Browser for Sqlite program, but not in Xojo?

[AlbertoD]

Without knowing what Xojo code you tried will be hard to help you.

[Eric_Bloom]

When a story is selected, you need to update the characters list box. You’ll need to do this with some Xojo code, probably in the SelectionChanged event of the stories list box. Xojo generally doesn’t automatically update controls.

If this doesn’t help, we might need more information to help you.

[Ian_Kennedy]

I would do the following:

• Use the RowTag of the listbox to store your Stories.ID value.
• when the row is selected use the RowTag to access the characters without any join.

SELECT * FROM characters where FKStory=ID

RowTag is any piece of data you wish to make it. So you can keep record keys in there without the need to make it visible at all.

[André_van_Haren]

This is the code I placed in the Character Listbox, it shows all the characters from all the stories in the database:

rs = app.DB.SelectSQL("SELECT * FROM characters INNER JOIN Stories on Stories.ID = Characters.FKStory WHERE Characters.ID = ?", ListBoxCharacters.RowTagAt(ListBoxCharacters.SelectedRowIndex).StringValue)

[André_van_Haren]

Yes, that is where the code is placed:
rs = app.DB.SelectSQL("SELECT * FROM characters INNER JOIN Stories on Stories.ID = Characters.FKStory WHERE Characters.ID = ?", ListBoxCharacters.RowTagAt(ListBoxCharacters.SelectedRowIndex).StringValue)

[Ian_Kennedy]

A couple of things. Is the StoryID a string? If not why use .StringValue? I would expect it to be an integer?

Also you originally wanted to select a story and show only the characters from that story. Now you are showing all characters.

[Mike_D]

Your two SQL statements are different:

WHERE stories.Story_title =

vs.

WHERE Characters.ID =

Are you doing the wrong WHERE clause in Xojo?

[AlbertoD]

First try to use the same query as the one used in DB Browser, that shows the same results?
Then you can use ‘?’.

[Scott_Griffitts]

If you’re looking for a story and all the characters associated with it, this simplified example might help illustrate:

var db as new SQLiteDatabase
var rs as rowset
var sql, x as string

call db.Connect

sql = "create table stories(id integer, story_name text)"
db.ExecuteSQL(sql)

sql = "create table characters(id integer, fkstory integer, character_name text)"
db.ExecuteSQL(sql)


sql = "insert into stories(id, story_name) values (?, ?)"
db.ExecuteSQL(sql, 1, "A Christmas Carol")
db.ExecuteSQL(sql, 2, "A Halloween Tale")
db.ExecuteSQL(sql, 3, "An Easter Fable")

sql = "insert into characters(id, fkstory, character_name) values (?, ?, ?)"
db.ExecuteSQL(sql, 1, 1, "Scrooge")
db.ExecuteSQL(sql, 2, 1, "TIny Tim")
db.ExecuteSQL(sql, 3, 2, "Dracula")
db.ExecuteSQL(sql, 4, 3, "Easter Bunny")

'select story and associated characters if the story name is A Christmas Carol
sql = "select * from " +_
"stories a " +_
"inner join characters b on b.fkstory = a.id " +_
"where a.story_name = ?"
rs = db.SelectSQL(sql, "A Christmas Carol")

while not rs.AfterLastRow
  x = x + "story: " + rs.Column("story_name").StringValue + " character: " + rs.Column("character_name").StringValue + EndOfLine
  rs.MoveToNextRow
wend 

MessageBox(x)

Edit: I originally used left join out of habit. Both return the same results in this example.

[Alexander_van_der_Linden]

You don’t need an inner join. Give Stories a column like STORY_ID which is unique and Characters have a foreign key STORY_ID. SQL ‘select * from CHARACTERS where CHARACTERS.STORY_ID = STORY.STORY_ID’

Every selected STORY.STORY_ID will give you the related characters IF the foreign key points to the correct Story. This is simple master/slave table.

[Scott_Griffitts]

That works assuming you don’t want any information from the story table at the same time.

A better approach would be to redesign the tables like:

story_table, character_table, author_table, genre_table, etc,

with cross reference tables like:

story_character_xref, story_author_xref, story_genre_xref, etc.

[Eric_Bloom]

Maybe you have it figured out now. If not, your code also needs to clear the characters list box and add rows to the list box from each database row in the updated query.

[André_van_Haren]

Using the same code in the DB program or in Xojo gives different results.
In DB Browser it shows the correct story characters:

in the Xojo Character listbox it shows all the characters from all stories:

[André_van_Haren]

Yers, it is doing this with a RefreshCharacterlist Method which rebuilds the Listbox.

Update:
Actually, I didn’t change the code here and now it only shows the correct story characters. This is great progress Now I have something to work with. Thanks for the hint.

[AlbertoD]

That means you have something wrong somewhere else in your code.