tags:

views:

55

answers:

1

Total newbie here, regarding sqlite, so don't flame too hard :)

I have a table:

index  name    length L  breadth B   height H
1      M-1234  10        5           2
2      M-2345  20        10          3
3      ....

How do I put some tabular data (let' say ten x,y values) corresponding to index 1, then another table to index 2, and then another, etc. In short, so that I have a table of x and y values that is "connected" to first row, then another that is connected to second row.

I'm reading some tutorials on sqlite3 (which I'm using), but am having trouble finding this. If anyone knows a good newbie tutorial or a book dealing with sqlite3 (CLI) I'm all ears for that too :)

+2  A: 

You are just looking for information on joins and the concept of foreign key, that although SQLite3 doesn't enforce, is what you need. You can go without it, anyway.

In your situation you can either add two "columns" to your table, being one x and another y, or create a new table with 3 "columns": foreign_index, x and y. Which one to use depends on what you are trying to accomplish, performance and maintainability.

If you go the linked table route, you'd end up with two tables, like this:

MyTable
index  name    length L  breadth B   height H
1      M-1234  10        5           2
2      M-2345  20        10          3
3      ....

XandY
foreign_index  x    y
1              12   9
2              8    7
3              ...

When you want the x and y values of your element, you just use something like SELECT x, y FROM XandY WHERE foreign_index = $idx;

To get all the related attributes, you just do a JOIN:

 SELECT index, name, length, breadth, height, x, y FROM MyTable INNER JOIN XandY ON MyTable.index = XandY.foreign_index;
voyager
I ran across that; but if I understood correctly, join requires a mutual column between two tables. I don't actually have that (I think!?). I need something that will enable me to (if you think of the above table as a group of cells) put an entire table in a cell in the first row, then another in 2nd and so on ... Of course, there is a possibility that I've completely misunderstood all this.
ldigas
@Idigas: you just `insert` on both tables with a common value, in the example, the values `12, 9` belong with the `name` `M-1234`
voyager