CODEWITHSUNDEEP
androidsqlite
Batman
Batman

Reputation: 49

Joining multiple SQL tables with table names in row of table

I am using SQLite and I'm not sure if this can be done. Consider I have these table:

======>Table: Total_Table

_id    Name   Ingredients

0     "A"     "table_a"<br>
1     "B"     "table_b"

=====>Table: table_a

 _id    Name

 0      "a_1" <br>
 1      "a_2"

=====>Table: table_b

_id    Name
0      "a_1" <br>

The tables referenced in the rows of Total_Table have a different number of rows and are created as rows in Total_Table are added.

At the moment, I need to structure a query which will return rows from Total_Table.

But will search through the entries of the Ingredients tables.

For example, if I am looking for all rows in Total_Table which have a_1 in their Ingredients table, it would return rows 0,1 from Total_Table.

Currently, I am doing this by querying all rows of Total_Table, then iterating through those entries and querying each table individually, forming an array as I go.

This is pretty bad since the tables I am looking through are relatively large, and hurts performance.

Any ideas? Thanks!

Upvotes: 1

Views: 89

Answers (2)

wsanville
wsanville

Reputation: 37516

I think you'll find this easier with a slightly different database schema. SQLite is a relational database, so let's try to store things accordingly. Basically, we'll have 3 tables, one for Total_Table, one for your ingredients, and a table to map which ingredients to which instance in the Total_Table.

Logically, it will look like this:

Table: Total_Table

_id    Name
0      "A"
1      "B"

Table: Ingredients

_id    Name

0      "a_1"
1      "a_2"

Table: Total_Ingredients

total_id  ingredient_id
0         0
0         1
1         0

The benefits here are many. First, you don't need to create any tables at runtime. You'll need exactly 3 tables, regardless of how many Total_Table entries you have. Second, it's easy to query. Third, you're not duplicating the name a_1 anywhere in the revised schema (this is called Database normalization).

Then, your query would look like this. Note that in the sample data, a_1 has an id of 0, which we'll use for querying purposes.

select _id, Name from Total_Table where id in 
  (select total_id from Total_Ingredients where ingredient_id = 0)

Update: Your other query is pretty similar if I understand correctly. So, you want the recipes which have some or all of a given ingredients. Suppose your subset includes ids 1, 5 and 7 for this example, the only change is to use an in clause.

select _id, Name from Total_Table where id in 
  (select distinct total_id from Total_Ingredients where ingredient_id in (1,5,7))

Upvotes: 1

waqaslam
waqaslam

Reputation: 68177

Try combining your tables as one (preferably a View) and then do a look up. Or run your query as below:

String name = "bla bla bla";

"SELECT DISTINCT _id FROM Total_Table WHERE _id IN
     ((SELECT _id FROM table_a WHERE Name='" + name + "') UNION ALL
      (SELECT _id FROM table_b WHERE Name='" + name + "'))"

Upvotes: 0

Related Questions