mysql连接，扩展结果

n53p2ov0 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(594)

我有两张table

Table name: place
Columns: placeid, name
Data: 1, My Favourite Donut Place

Table name: category
Columns: categoryid, name, placeid
Data: 1, Donuts, 1
      2, Coffee, 1
      3, Hot Chocolate, 1

我像这样加入两个人：

select p.name as place, c.name as category from place p join category c on p.placeid=c.placeid

会得到这样的结果：

place                     category
My favourite donut place  Donuts
My favourite donut place  Coffee
My favourite donut place  Hot Chocolate

现在我想搜索提供咖啡的地方：

select p.name as place, c.name as category from place p join category c on p.placeid=c.placeid where c.name = 'Coffee'

结果如下：

place                     category
My favourite donut place  Coffee

但我还是想展示一下“我最喜欢的甜甜圈店”提供咖啡的所有不同类别，即“甜甜圈”和“热巧克力”。
要做到这一点，最好的方法是什么？

mysql Join extend

来源：https://stackoverflow.com/questions/51196197/mysql-joining-extending-the-result

1条答案

按热度按时间

enyaitl31#

您只需执行第二次从位置到类别的连接，就可以获得所有类别，而连接顺序在这种情况下并不重要，我发现这种顺序使它更清晰。

SELECT p.name AS place, placeCategories .name AS category 
FROM category AS filterCategory 
INNER JOIN place AS p ON filterCategory.placeid=p.placeid 
INNER JOIN category AS placeCategories ON p.placeid=placeCategories.placeid 
WHERE filterCategory.name = 'Coffee'

赞(0）回复(0）举报 2021-06-20

我来回答

mysql连接，扩展结果

1条答案

相关问题

热门标签

最新问答