用关系表作为mysql的中介从3个表中获取列数据

Question

我也想获取类别名。我有下表

积表

mysql> describe prod;
+----------+-----------------+------+-----+---------+----------------+
| Field    | Type            | Null | Key | Default | Extra          |
+----------+-----------------+------+-----+---------+----------------+
| pid      | int(4) unsigned | NO   | PRI | NULL    | auto_increment |
| pro_name | varchar(32)     | NO   |     | NULL    |                |
+----------+-----------------+------+-----+---------+----------------+

分类表

mysql> describe cat;
+----------+-----------------+------+-----+---------+----------------+
| Field    | Type            | Null | Key | Default | Extra          |
+----------+-----------------+------+-----+---------+----------------+
| cid      | int(4) unsigned | NO   | PRI | NULL    | auto_increment |
| cat_name | varchar(32)     | NO   |     | NULL    |                |
+----------+-----------------+------+-----+---------+----------------+

cat_pro表关系表

mysql> describe cat_pro;
+----------+-----------------+------+-----+---------+----------------+
| Field    | Type            | Null | Key | Default | Extra          |
+----------+-----------------+------+-----+---------+----------------+
| cat_id   | int(4) unsigned | NO   |     | NULL    |                |
| pro_id   | int(4) unsigned | NO   |     | NULL    |                |
+----------+-----------------+------+-----+---------+----------------+

我的Current Query：

mysql> select pid, pro_name, cat_pro.cat_id 
       from prod 
       left join cat_pro on cat_pro.pro_id=prod.pid 
       where pid='2';

但是，当我运行这个命令时，它会给出错误表示unknown column cat_pro.cat_id in on clause

mysql> select pid, pro_name, cat_pro.cat_id, cat.cat_name 
       from prod 
       left join cat_pro on cat_pro.pro_id=prod.pid 
       left join cat on cat.cid=cat_pro.cat_id 
       where pid='2'

有没有办法，我也可以从表中获取类别名，还是必须运行另一个query从cat表中获取类别名。

谢谢

Answer 1

查询：

select pid, pro_name, cat_pro.cat_id, cat.cat_name
from
prod
    inner join
cat_pro
    on (cat_pro.pro_id = prod.pid)
    inner join
cat
    on (cat.cid = cat_pro.cat_id)
where pid='2';

为您提供的db结构工作，没有错误。

Answer 2

希望这对你有帮助。在下面的查询中，我们使用子查询来获取基于category_id的类别名称。

 select 
 prod.pid, 
 prod.pro_name, 
 cat_pro.cat_id,
 (
    select 
        cat_name
    from 
        Category c
    where 
        c.cid = cat_pro.cat_id

 ) as category_name

 from 
 prod left join 
 cat_pro on cat_pro.pro_id=prod.pid 
 where prod.pid='2';

Answer 3

试试这个：

SELECT `pid`, `pro_name`, `c`.`cat_id`
FROM `prod` `p`
LEFT OUTER JOIN `cat_pro` `c` ON `c`.`pro_id` = `p`.`pid`
WHERE `p`.`pid` = 2;