我有两个表
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
但我仍然想展示供应咖啡的"我最喜欢的甜甜圈店"的所有不同类别,即"甜甜圈"和"热巧克力"。
实现这一目标的最佳方法是什么?
您只需将位置连接到类别即可获得所有类别,而在这种情况下连接顺序并不重要,但我发现这种顺序使其更清晰。
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'