Bryan Dizon Bryan Dizon - 4 months ago 5
SQL Question

How to make 1 query to 3 tables

I need to make one query resulting to a one column result from Recipe, Ingredients and Methods Table. My Example Tables:

I am getting the Recipe Name, Recipe Contributor, Recipe Description,
Ingredients for a recipe as well for its methods

Recipe Table

+--------------------------------------------------------------------+
|RECIPE ID | RECIPE NAME | RECIPE CONTRIBUTOR | RECIPE DESCRIPTION |
----------------------------------------------------------------------
| 1 |Chicken Curry | Me | Spicy and Hot |
+---------------------------------------------------------------------


Ingredients Table

+----------------------------------+
|ING ID | ING NAME | RECIPE ID |
------------------------------------
|1 |Chicken | 1 |
------------------------------------
|2 |Curry Powder | 1 |
+----------------------------------+


Methods Table

+----------------------------------+
|MET ID | METHOD | RECIPE ID |
------------------------------------
|1 |Boil Chicken | 1 |
------------------------------------
|2 |Cook curry | 1 |
+----------------------------------+


Desired Output:

+---------------+
|Output Column |
-----------------
Chicken Curry
Me
Spicy and Hot
Chicken
Curry Powder
Boil Chicken
Cook curry
+----------------+

Answer

As far as I understood you want to combine different columns from different tables into a single column output in order to use it in your Android app (previously the question had android tag). Below find my solution:

String query = 
"select \"RECIPE NAME\" AS output from Recipe
union all
select \"RECIPE CONTRIBUTOR\" from Recipe
union all
select \"RECIPE DESCRIPTION\" from Recipe
union all
select \"ING NAME\" from Ingredients
union all
select METHOD from Methods";

Cursor cursor = sqLiteDatabase.rawQuery(query, null);

Then iterate over the cursor with cursor.getString(0); to retrieve each row

Comments