SQLite - cross reference search

[quailcreek]

Could use some help with some SQLite code.
I have a number of objects (x) that are consumed by another object (y) in the DB. The names of each object, x and y, are unique. All x objects are in one table and all y objects are in another table. Let’s call the tables xTable and yTable. Any x object can be consumed or occur in any y object but only once in each y object. What I need to accomplish is get a list of the y objects that have consumed a x object.

Here’s a simple scenario of what I’m trying to accomplish. I’ll use fruit and fruit baskets to explain. Let’s say I have a bunch of different fruit and I was to tell what fruit has been added to what basket.

I have a table called “Baskets” and a table called “Fruit”. The user will be defining the names of the baskets and the names of the fruit by text entry.

I need to search “Banana” and get a list of the baskets that have Bananas in them. I have a another table called CrossRef which is empty except for a “RefID” column. The columns are named for each fruit object and the basket names are stored in the corresponding fruit column.

When the users creates a fruit object.

Code: Select all

## Because columns cannot contain spaces.
replace space with "_" in pFruit_Name
      
## Add the name to the CrossRef Table
put "ALTER TABLE CrossRef ADD '"&pFruit_Name&"' varchar(255)" into tSQLStatement
revExecuteSQL sDatabaseID,tSQLStatement

When the user does the search for the baskets, this is what I have so far to get the list of baskets.

Code: Select all

## Populate the variable with the name of the fruit
put item 1 of tData into tThisFruit
## Because columns cannot contain spaces
replace space with "_" in tThisFruit

## This does not return the list. If I replace ‘"&tThisFruit&"' with the column name it works fine
put "SELECT '"&tThisFruit&"' FROM CrossRef" into tSQLStatement2

answer tSQLStatement2
put revDataFromQuery(tab,return,sDatabaseID,tSQLStatement2) into tData2
filter tData2 without empty
answer tData2

If anyone has suggestions or if you think of a better way to handle this please let me know.

[phaworth]

If I understand this correctly, what you want to do is return the names of all the baskets that contain a specific fruit. If so, there's no need for a crossref table.

Your Fruits table should have the following columns:

FruitID INTEGER PRIMARY KEY
FruitName TEXT UNIQUE COLLATE NOCASE
<any other columns you need>

The UNIQUE and COLLATE NOCASE makes sure each fruit name in the table is unique no matter what combination of upper and lower case letters are in it

Your Baskets table should look like this:

BasketID INTEGER PRIMARY KEY
FruitID INTEGER REFERENCES Fruits.FruitID
BasketName (Text)
<any other columns you need>

The SELECT statement then becomes:

SELECT BasketName FROM Baskets WHERE Baskets.FruitID=(SELECT FruitID from Fruits WHERE FruitName='Banana')

HTH,

Pete

[Adrien]

A "1 to n" relationship can be understood as the following:
- each basket has an unique identifier
- each fruit has an unique identifier
- a basket may contain multiple fruits at once
- a fruit may be contained in an existing basket and if so, in only one basket

For this, you would need the following tables:
- table_basket, with at least an id_basket column
- table_fruit, with at least an id_fruit column and an id_basket column (the id_basket is a REFERENCE from table_basket, and if your fruit MUST be placed in a basket, make it NOT NULL)
the table_fruit.id_basket value contains the unique identifier for the basket the fruit is in (this unique fruit belongs to one basket, and it is the basket number X)

For instance, you can SELECT an id_fruit FROM the table_fruit WHERE table_fruit.id_basket is known.
Or the other way around, you can SELECT the table_fruit.id_basket FROM the table_fruit WHERE the table_fruit.fruit_name is Banana.


Note: in Pete's example just above, the id_fruit is in the table_basket table. This means it works the other way around, where a basket can only contain one fruit and a fruit can be found (referenced) in multiple baskets!

---

A "n to n" relationship can be understood as the following:
- each basket has an unique identifier
- each fruit has an unique identifier
- a basket may contain multiple fruits at once
- a fruit may be contained in multiple baskets at once

For this, you would need the following tables:
- table_basket, with at least an id_basket column
- table_fruit, with at least an id_fruit column
- mix_fruitbasket, with at least an id_fruit column and an id_basket column. The primary key of this table is the combination of "id_basket+id_fruit"

This way, in the mix_fruitbasket table, you can have:
fruit 1 | basket 1
fruit 2 | basket 1
fruit 1 | basket 2
fruit 3 | basket 3
fruit 1 | basket 3
fruit 3 | basket 2
this means, the fruit 1 belongs to baskets 1, 2 and 3, fruit 2 belongs to basket 1, fruit 3 belongs to basket 2 and 3...

Here, you can SELECT in the table_fruit INNER JOIN mix_fruitbasket USING (id_fruit) an id_fruit WHERE mix_fruitbasket.id_basket is known.
Or something else, like a SELECT table_basket.color FROM mix_fruitbasket INNER JOIN table_basket USING (id_basket) INNER JOIN table_fruit USING (id_fruit) WHERE table_fruit.fruit_weight > 300
That would give you every basket color containing a fruit over 300 grams.

Make sure to GROUP BY and ORDER BY your results at your convenience.

I hope this makes it a bit clearer for you and that of course I'm not writing wrong stuff, that would be bad.

[quailcreek]

Pete and Adrien,
Please excuse me for not replying right away. You each provided a lot of information and I'm trying to think it through. I'm new to SQL as you can probably tell. I do need to cross reference both directions. Fruit to baskets and baskets to fruit so I need to work out a combination from your information.

Pete, I like the idea of nesting a SELECT inside a SELECT. A very nice shortcut and cross referencing the unique IDs is much easier and simpler than FruitNames and BasketNames. Although it is a little confusing to me right now.

Adrien, The "n to n" relationship is the way I need to go. Believe me, you're not writing bad stuff. This is very good.
What would the SELECT statment for this look like?
-- mix_fruitbasket, with at least an id_fruit column and an id_basket column. The primary key of this table is the combination of "id_basket+id_fruit"

[Adrien]

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

[phaworth]

There is no n-n relationship between fruits and baskets. Gettin a list of baskets containing a specific fruit can be done either as I suggested or with the other suggested method of a JOIN.

The listt of fruits in a basket is simply a list of all the rows in the basket table with a specific basket name, something like:

SELECT fruitname from baskets LEFT JOIN fruits ON fruits.fruitid=baskets.fruitid WHERE basket name='mybasket'

You can add ORDER BY clause if you want to sort them. No need for a cross reference table.

If you're looking for an sqlite admin program, check out my SqliteAdmin program at www.lcsql.com. It's much easier to try different things with it than writing Livecode scripts.

[SparkOut]

What Pete said ^

[Adrien]

Any x object can be consumed or occur in any y object but only once in each y object.

which is

Each object X can be linked to any object Y, but no more than twice to the same object Y

the missing data is: can one object X be linked to two or more different objects Y?
and the opposite: can one object Y be linked to two or more different objects X?

If yes, to me this is indeed a "n to n" relationship, where:
x1 | y1
x2 | y1
x1 | y2
x2 | y2
is valid. Adding another "x1 | y1" would trigger the "object x found only once in each object y", hence the combo "x+y" makes the composite primary key in the mix table.

But ultimately, maybe the fruit example is easy to grasp, but might not represent exactly the real situation.

Only quailcreek knows


Cheers,

[quailcreek]

I think the best path is to work with the FruitID and BasketID in a CrossRef table.
The user creates the FruitName and BasketName and they can also rename them both.
This is why I need to, for the CrossRef table at least, deal with the FruitID and BasketID.

A fruit can be in any or all baskets but only once in each basket.
I need to be able to query the all the baskets a given fruit is in.
I need to be able to query al the fruit in a given basket.

You’re correct, Adrien. My app isn’t dealing with fruit and baskets but this is a really good analogy.

I’ve create the CrossRef table thus:
CREATE TABLE CrossRef (FruitID INTEGER REFERENCES MyFruit, BasketD INTEGER REFERENCES MyBaskets, PRIMARY KEY (FruitID, BasketID))

After I’ve inserted the FruitID and BasketID like this and it works fine:
put "INSERT into CrossRef (FruitID,BasketID) VALUES ('"&tFruitID&"','"&tBasketID&"')" into tSQLStatement
revExecuteSQL sDatabaseID,tSQLStatement


Now I need to write the two SELECT statements. There are two tables. MyFruit with the fruit attributes and MyBaskets with the basket attributes.

——————————-
One for querying the basketNames a fruit is in based upon the FruitID. This is working fine.

put fld “Fruit_ID” into tFruitID
put "SELECT MyBaskets.BasketName FROM CrossRef INNER JOIN MyFruit USING (FruitID) INNER JOIN MyBaskets USING (BasketID) WHERE MyFruit.FruitID = :1" into tSQLStatement
put revDataFromQuery(tab,return,sDatabaseID, tSQLStatement,”tFruitID") into tData
——————————-

One for querying the fruitNames in a basket based upon the BasketID. This one isn’t working either.

put fld “Basket_ID” into tBasketID
put "SELECT CrossRef.BasketID FROM CrossRef INNER JOIN MyFruit USING (FruitID) WHERE MyFruit.FruitName = :1 GROUP BY CrossRef.BasketID" into tSQLStatement
put revDataFromQuery(tab,return,sDatabaseID, tSQLStatement,"tBasketD") into tData
——————————-

[Adrien]

One for querying the fruitNames in a basket based upon the BasketID.

I think this should be :

Code: Select all

put fld “Basket_ID” into tBasketID
put "SELECT MyFruit.FruitName FROM CrossRef INNER JOIN MyFruit USING (FruitID) WHERE CrossRef.BasketID = :1 GROUP BY CrossRef.BasketID" into tSQLStatement
put revDataFromQuery(tab,return,sDatabaseID, tSQLStatement,"tBasketD") into tData

And for your first query, you don't need both INNER JOINs:

One for querying the basketNames a fruit is in based upon the FruitID.

You can directly look for the FruitID in the CrossRef table, and you have then no information to read in the MyFruit table.
You can remove the "INNER JOIN MyFruit USING (FruitID)" and change the WHERE part, unless you want to use the FruitName instead of the FruitID in the WHERE clause.

Code: Select all

put fld “Fruit_ID” into tFruitID
put "SELECT MyBaskets.BasketName FROM CrossRef INNER JOIN MyBaskets USING (BasketID) WHERE CrossRef.FruitID = :1" into tSQLStatement
put revDataFromQuery(tab,return,sDatabaseID, tSQLStatement,”tFruitID") into tData

[phaworth]

Once again, you don't need a crossref table, it needlessly complicates things. The two select statements I gave you achieve both of the queries you mentioned.

The foreign key definition I noted in my first reply takes care of any renaming of fruits. Instead of storong the fruit name in the baskets table, you store its key value so if the fruit name is changed, its key value remains the same.

If a fruit can only appear once in a basket, you can take care of that with a UNIQUE clause the indicates that eac combination of basketid an fruitid !ust be unique:

UNIQUE basketid,fruitid

It's hard to cover all the intricacies of swl in a single forum post. As you've said you are a newcomer to sql, I strongly recommend you find some good online educational materials before rushing in to your database design.. Try w3schools.cpm or Devin Assay's great BYU pages - I don't have the URL in front of me but a forum search should turn it up.

Another great resource is the sqlite users forum. Your questions are specific to sqlite not Livecode so the sqlite forum will be more suitable. Once again, don't have the url in front of me but just Google sqlite forum.

Good luck!

[quailcreek]

Pete, I appreciate the SQLite references information. I have Adrien's method working and I’d like to get the method you proposed working so I can compare. If that's OK.

As I mentioned earlier.
A fruit can be in any or all baskets but only once in each basket.
I need to be able to query all the baskets a given fruit is in.
I need to be able to query al the fruit in a given basket.

Pete, if I want to cross reference both directions wouldn’t I need the two table to look something like this?

Create the MyFruit table:

Code: Select all

put "CREATE TABLE MyFruit(FruitID INTEGER PRIMARY KEY AUTOINCREMENT, BasketID INTEGER REFERENCES MyBaskets.BasketID,FruitName TEXT UNIQUE COLLATE NOCASE)" into tSQLStatement1
revExecuteSQL sDatabaseID, tSQLStatement1

Create the MyBasket table:

Code: Select all

put "CREATE TABLE MyBaskets(BasketID INTEGER PRIMARY KEY AUTOINCREMENT,FruitD INTEGER REFERENCES MyFruit.FruitID,BasketName TEXT UNIQUE COLLATE NOCASE)" into tSQLStatement2
revExecuteSQL sDatabaseID, tSQLStatement2

Right now, when a fruit is added to a basket, I’m inserting it like this. It populates both directions. How would this be handled using your method?

Code: Select all

put "INSERT into CrossRef (TrickID,RoutineID) VALUES ('"&tlTrick_ID&"','"&tlRoutine_ID&"')" into tSQLStatementC
revExecuteSQL sDatabaseID,tSQLStatementC

Would this SELECT statement still be valid?

Code: Select all

SELECT BasketName FROM MyBaskets WHERE MyBaskets.FruitID=(SELECT FruitName from MyFruit WHERE FruitID=‘“&tFruitID&”’)

[phaworth]

Your myFruit table does not need the foreign key reference to myBaskets. Your create statement for myBaskets is correct.

My original reply has the SELECT statement for how to get a list of baskets containing a specific fruit. One of my later replies has the SELECT for listing all the fruits in a basket. Neither one of those queries needs a cross reference table, the necessary structures are already in place in the myFruit and myBaskets tables. Please try those SELECTs and you will see that they work just fine and are much simpler than maintaining a cross reference table and all the hassle that entails.

Good luck!

Pete

[quailcreek]

Pete, I'm trying to drink the cool-aid. I don't understand how to set the relationship when a fruit is added to a basket. With the CrossRef table method I used the INSERT I posted above. If you would point me in the right direction I would be grateful.

[Adrien]

I think Tom is looking for a "n to n" relationship, which I believe can only work with a mix table, or I'd need to learn something new here.

However, in a "1 to n" relationship, you will only need two tables. To make sure this is clear, I will change the example to: songs and albums.
So for your "1 to n" relationship, you need:
an album table, with at least one column: album_id (auto increment)
a song table, with at least two columns: song_id (auto increment) and album_id (ext ref to album table)

This means, multiple different songs can reference a same album, or in other words, multiple different songs "belong" to one album, and one song belongs to only one album.
If you need one song to belong to two or more albums, it turns into a "n to n", and you will need a mix table (or am I wrong? Or that would mean you would have to deactivate the auto incrementation, and duplicate rows on the same song_id and other cols like song_name, but change the album_id value.. waste of space and hassle to keep it updated on every field..).


----

By the way, remember that you can create complex indexes (references) after you created all your tables! You will get errors if you try to set a reference to another table that hasn't been created yet, I believe.

Happy coding, cheers! Glad you had something working at least, trial and error is long but great for learning