具有内部连接和限制的mysql子查询

Question

我有两个表recipes_sa，其中包含以下列:

recipes_id   recipes_name   recipes_chef
----------   ------------   ------------

和 chefs_sa 的列:

chefs_id   chefs_name
--------   ----------

我想使用 INNER JOIN 和 LIMIT 获取有限数量的食谱及其厨师详细信息

我实现了以下功能:

function getLimitJoinData($data, $tbls, $ids, $abr, $type, $limit) {

            $dataToSelect = implode($data, ',');

            $q = "SELECT $dataToSelect";

            $q.= " FROM (SELECT * FROM $tbls[0] LIMIT $limit) $abr";


            for ($i=1; $i < count($tbls); $i++) { 
                $q .= " ".$type." JOIN ". $tbls[$i] ." ON " . $abr.'.recipes_chef' .' = '. $ids[$i-1][0];   
            }
        }

查询是这样的

SELECT chefs_sa.chefs_name,
       recipes_sa.recipes_name 
FROM (SELECT * FROM recipes_sa LIMIT 8) rec 
INNER JOIN chefs_sa ON rec.recipes_chef = chefs_sa.chefs_id

但是当我运行查询时，我收到了以下警告:

Warning: PDO::query(): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'recipes_sa.recipes_name' I don't understand why

我在 recipes_sa 表中有列 recipes_name，从我读到的数据库运行“内部查询”(那个有限制)首先，然后是如何找不到 recipes_name 列!!

Answer 1

您为 recipes_sa 添加了别名 AS 记录。使用以下内容:

SELECT chefs_sa.chefs_name,
       rec.recipes_name 
FROM (SELECT * FROM recipes_sa LIMIT 8) rec 
INNER JOIN chefs_sa ON rec.recipes_chef = chefs_sa.chefs_id