Glad it helps, and don't worry about repliyng ASAP. We all have busy times
You surely know the SQLite website, and more precisely its syntax page :
http://www.sqlite.org/lang.html
Which I find very usefull, I often go there to remember my syntax, and it's mostly good for MySQL too!
If you need a clearer view on JOINs, have a look there :
http://blog.codinghorror.com/a-visual-e ... sql-joins/
Make sure to adapt some keywords to SQLite.
Nesting SELECTs inside SELECTs (or other functions/statements!) is easier to do in the beggining, and sometimes the best way! However I've read that there are "cleaner" solutions. Note the quotes, as I am new to SQL myself.
So, you'll need your mix table, with a composite primary key. Make sure you create the fruit and basket tables first, or you'll have references issues!
I believe it is: CREATE TABLE IF NOT EXISTS mix_fruitbasket (fruit_id INTEGER REFERENCES table_fruit, basket_id INTEGER REFERENCES table_basket PRIMARY KEY (fruit_id, basket_id))
There are many ways to do the CREATE TABLE statement, for instance you can chose to create the FOREIGN KEYs after the PRIMARY KEY, you can create a "raw" table first and then in another statement, you can create your indexes...
You can add any other columns to it, to fit your needs, like a "status" column, or a "mix_id" number column, that may or may not be unique... Up to you!
For your SELECT query, let's say you have the proper tables with the proper column names and so on. If I understood you well, you might need to:
Code: Select all
put "SELECT table_basket.basket_name FROM mix_fruitbasket INNER JOIN table_fruit USING (fruit_id) INNER JOIN table_basket USING (basket_id) WHERE table_fruit.fruit_name = :1" into tSQLiteQry
revExecuteSQL sSQLiteID, tSQLiteQry, "Banana"
Explanation: you check to fruit_name in the table_fruit first. You select only the rows those that have fitting name. Then, using the fruit_id from the rows you just selected in table_fruit, you select now the rows where those fruit_id appear in the mix_fruitbasket table. Then, using the basket_id from the rows you just selected in the mix_fruitbasket table, you select all the basket names with the corresponding basket_id! If the basket_name column has the UNIQUE attribute, you will not have duplicated names in your results. However, if for some reason you have two different baskets (different basket_id) with the same name, then you will!
BUT if you only need to get the basket_id, it gets a stage simpler:
Code: Select all
put "Banana" into FruitName
put "SELECT mix_fruitbasket.basket_id FROM mix_fruitbasket INNER JOIN table_fruit USING (fruit_id) WHERE table_fruit.fruit_name = :1 GROUP BY mix_fruitbasket.basket_id" into tSQLiteQry
revExecuteSQL sSQLiteID, tSQLiteQry, FruitName
Explanation: the basket_id is already in the mix_fruitbasket table, so you have no need to fetch information in the table_basket, hence there is no need to include a JOIN on it in the query! There, the GROUP BY keyword is here to filter out any duplicate: you might have two different Bananas in the same basket, so it would have appeared twice.
Note1: to use the FROM table1 INNER JOIN table2 USING (colname) shortcut, you need to have those two (colname) in both tables 1 and 2, with the same data type (int, text, real...)!
Note2: as you can see, I have used a prepared statement (with the :1 and the "Banana" argument / "FruitName" variable in the revExecuteSQL calls). This could be new to you, if so, welcome to this wonderful world!
Note3: feel free to get rid of the table names in tablename.colname if there are no ambiguities: the fruit_name column should only be the in table_fruit table, however, as said as in Note1, you'll have to keep the table names for your key columns... "mix_or_table.thing_id"
Note4: I have changed the variable names like sSQLiteID, just to make sure you don't copy paste it right away without spending some time thinking on it! I'm evil I know, sorry.
Oh, and I am at work at the moment, I have no way to check if all of this actually run, so that's why I'm hoping everything's alright...
Cheers,
Adrien