mysql - 如何对 3 个表进行 1 次查询

Question

我需要进行一次查询，得出配方、成分和方法表中的一列结果。我的示例表:

我正在获取食谱名称、食谱贡献者、食谱描述、食谱的成分及其方法

配方表

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

成分表

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

方法表

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

期望的输出:

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

Answer 1

据我了解，您希望将不同表中的不同列组合成单个列输出，以便在 Android 应用程序中使用它(之前的问题有 android 标签)。下面找到我的解决方案:

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);

然后使用 cursor.getString(0); 迭代游标以检索每一行