mysql - 如何从两个表中进行选择？

Question

我有表“文章”

+-------------+|   articles  |+----+--------+| id | title  |+----+--------+| 1  | title1 |+----+--------+| 2  | title2 |+----+--------+| 3  | title3 |+----+--------+

表“目录”

+---------------------+|      catalogue      |+----+--------+-------+| id | group  | name  |+----+--------+-------+| 1  | group1 | name1 |+----+--------+-------+| 2  | group1 | name2 |+----+--------+-------+| 3  | group2 | name3 |+----+--------+-------+| 4  | group2 | name4 |+----+--------+-------+

绑定(bind)表“bindTable”

+------------+--------------+-------+|              bindTable            |+------------+--------------+-------+| id_article | id_catalogue | value |+------------+--------------+-------+|     1      |      2       |   1   |+------------+--------------+-------+|     1      |      3       |   4   |+------------+--------------+-------+|     3      |      1       |   2   |+------------+--------------+-------+|     3      |      3       |   1   |+------------+--------------+-------+|     3      |      4       |   3   |+------------+--------------+-------+

我需要获得表“结果”中的结果，其中我可以从表“文章”中获取所选项目的“catalogue_name:值”对

+-----------------------------------------------------+|                        result                       |+------------+---------------+----------------+-------+| article_id | article_title | catalogue_name | value |+------------+---------------+----------------+-------+|      1     |     title1    |  group1_name2, |   1   ||            |               |  group2_name3  |   4   |+------------+---------------+----------------+-------+|      3     |     title3    |  group1_name1, |   2   ||            |               |  group2_name3, |   1   ||            |               |  group2_name4  |   3   |+------------+---------------+----------------+-------+

谁能告诉我一个数据库查询的查询字符串？感谢您的关注。

My vision:     SELECT b.id_article, a.title, c.group, c.name, b.value     FROM bindTable b    JOIN articles a ON a.id = b.id_articles    JOIN catalogue c ON c.id = b.id_catalogue    WHERE b.id_article = 1

但我需要一行包含 c.name&b.value 对的 a.id

Answer 1

带有内连接的选择

select a.article_id, a.article_title, b.catalogue_name, b.value
from bindTable as c
inner join articles as a on a.id = c. article_id
inner join catalogues as b on c. id_catalogue = b.id