SELECT a list of elements and 5 tags for each one

Posted by Vittorio Vittori on Stack Overflow See other posts from Stack Overflow or by Vittorio Vittori
Published on 2010-03-11T20:16:15Z Indexed on 2010/03/11 20:19 UTC
Read the original article Hit count: 329

Filed under:
|
|
|
|

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?

© Stack Overflow or respective owner

Related posts about query

Related posts about mysql