MySQL joining , extending the result

Question

I have two tables

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

I join the two like this:

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

And would get a result like this:

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

Now I want to search for places that serve coffee:

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

Result like this:

place                     category
My favourite donut place  Coffee

But I still want to show all the different categories for "My favourite donut place" that serves coffee, i.e. "Donuts" and "Hot Chocolate" too.

What would be the best way to go about to accomplish that?

Answer 1

You can just do a second join of place to category to get all categories, while the join order does not really matter in this case, I find this ordering makes it clearer.

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'