SELECT a list of elements and 5 tags for each one

Question

Hi, I'm trying to query a set of buldings listed on a table, these buildings are linked with tags. I'm able to do it, but my problem is how limit the number of tags to see:

table buildings
id    building_name     style
1     Pompidou          bla
2     Alcatraz          bla
3     etc.              etc.

table tags // they can be 50 or more per building
id    tag_name
1     minimal
2     gothic
3     classical
4     modern
5     etc.

table buildings_tags
id    building_id     tag_id

I though to do something like this to retrieve the list, but this isn't compplete:

SELECT DISTINCT(tag), bulding_name
FROM buldings
INNER JOIN buildings_tags
ON buildings.id = buildings_tags.building_id
INNER JOIN tags
ON tags.id = buildings_tags.tag_id
LIMIT 0, 20

// result

building     tag
Pompidou     great
Pompidou     france
Pompidou     paris
Pompidou     industrial
Pompidou     renzo piano     <= How to stop at the 5th result?
Pompidou     hi-tech
Pompidou     famous place
Pompidou     wtf
etc..        etc...

this query loads the buildings, but this query loads all the tags linked for the building, and not only 5 of them?

Answer 1

It seems to me what you're asking is different from what your query is doing. If I understood you right, this query should do what you need:

SELECT bulding_name, tag
FROM   buldings b
  LEFT JOIN (SELECT tag, building_id
              FROM   tags
                INNER JOIN buildings_tags
                  ON tags.id = buildings_tags.tag_id
              WHERE  building_id = b.id
              LIMIT  5) t
    ON b.id = t.building_id
ORDER BY bulding_name

This would get you all the buildings, with at most 5 tags for each. There might well be a more elegant way to do it though.

Answer 2

If I understand your idea, you should change:

LIMIT 0,20

by:

LIMIT 0,5

or just:

LIMIT 5

That's all!

Answer 3

SELECT a.building_name, a.tag_name
FROM (
 SELECT b.building_name, t.tag_name, bt.building_id
 FROM tags t
  INNER JOIN buildings_tags bt ON (bt.tag_id=t.id)
  INNER JOIN buildings b ON (b.id=bt.building_id)
 ORDER BY bt.building_id
) a
,
(SELECT @prev:=-1, @count:=1) b
WHERE
 CASE WHEN @prev<>a.building_id THEN
   CASE WHEN @prev:=a.building_id THEN
    @count:=1
   END
 ELSE
   @count:=@count+1
 END <= 5

If this match your needs then explanation for approximatively the same query here