Android Sqlite Statement Question

Question

Lets say I have a database with tables called box, item, and box_contents and I want to get any item that is contained in the box_content table (all items, but discard the ones that arent in the box_contents table). What would be the correct sqllite syntax for this?

Answer 1

Depends on your schema, that is, how does your "box_content" table denote that it "contains" a certain item. For example, if it does so by having a column "ItemId" that's a foreign reference to the "item" table's primary key "Id", then

SELECT item.*
FROM item
JOIN box_content ON (box_content.ItemId = item.Id)

would work fine (assuming you do mean exactly what you say, i.e. that you want items contained anywhere in the "box_content" table, and that your mention of yet another table "box" is actually completely irrelevant).

But, if your schema is completely different (or what you actually want to do is quite different from what you said), then of course so will the needed SQL. What about editing your Q to show the CREATE TABLE statements and (if needed) any comments abut them...?

Answer 2

You will want to do what's called a join between the two tables.

You'd have something like:

SELECT (item_columns)
FROM item i JOIN box_contents bc ON i.item_id = bc.item_id