MySQL: Limit a query to 10 rows where every index has 5 tags.

Question

Hi, I'd like to make a query where I can retrieve an interval of 10 rows with 5 tags per each one.

I have houses and tags table, I know how to do it only for simple queries with a SELECT using LIMIT but how can I do it in this case?

table houses
id    house            country
1     Grunt Mansion    us
2     Hororo Suneku    jp
3     Casa Cattani     it
4     Sweet Home       uk
5     Heinzvaiter      de
6     F56X-5           us
7     Swan Jong        cn
8     Drupnowevo       ru
9     Bambagiador      br
10    El Stanco        es

table houses_tags
id    id_house    id_tag
1     1           1
2     1           2
3     1           3
4     1           4
5     2           1
6     2           2
7     2           3
8     2           4

table tags
id    tag
1     minimal
2     baroque
3     cosy
4     simple

If a make a query like this to get the first 10 houses:

SELECT *
FROM houses
LEFT JOIN (
    SELECT *
    FROM tags
    INNER JOIN houses_tags
    ON id_house = houses.id
    LIMIT 5
) house_tag
LIMIT 0, 10

I get something like this:

query result
row    house            country    tag      id_house     id_tag
1      Grunt Mansion    us         minimal  1            1
2      Grunt Mansion    us         baroque  1            2
3      Grunt Mansion    us         cosy     1            3
4      Grunt Mansion    us         simple   1            4
5      Hororo Suneku    jp         minimal  2            1
6      Hororo Suneku    jp         baroque  2            2
7      Hororo Suneku    jp         cosy     2            3
8      Hororo Suneku    jp         simple   2            4
9      Casa Cattani     it         NULL     NULL         NULL
10     Sweet Home       uk         NULL     NULL         NULL

My problem is I get only the first 10 rows cutting out the last houses from the query because the tags of the first ones occupy all the rows

Can I write a query where I can retrieve the first 10 houses and 5 tag per each one?

query result
row    house            country    tag      id_house     id_tag
1      Grunt Mansion    us         minimal  1            1
2      Grunt Mansion    us         baroque  1            2
3      Grunt Mansion    us         cosy     1            3
4      Grunt Mansion    us         simple   1            4
5      Hororo Suneku    jp         minimal  2            1
6      Hororo Suneku    jp         baroque  2            2
7      Hororo Suneku    jp         cosy     2            3
8      Hororo Suneku    jp         simple   2            4
9      Casa Cattani     it         NULL     NULL         NULL
10     Sweet Home       uk         NULL     NULL         NULL
11     Heinzvaiter      de         NULL     NULL         NULL
12     F56X-5           us         NULL     NULL         NULL
13     Swan Jong        cn         NULL     NULL         NULL
14     Drupnowevo       ru         NULL     NULL         NULL
15     Bambagiador      br         NULL     NULL         NULL
16     El Stanco        es         NULL     NULL         NULL

At the end I should need a result I display like this example:

Mansions          Tags
Grunt Mansion     minimal, baroque, cosy, simple
Hororo Suneku     minimal, baroque, cosy, simple
Casa Cattani      -
Sweet Home        -
Heinzvaiter       -
F56X-5            -
Swan Jong         -
Drupnowevo        -
Bambagiador       -
El Stanco         -

pages 1 | 2 | 3

can I do it?

Answer 1

I think you want your subselect to give you back the 10 houseID's you want results for, then just left join against the tags table subselect (limit 5)

Answer 2

MySQL doesn't have analytic functions, which is what you'd use for this before LIMIT:

    SELECT x.id,
           x.house,
           x.country,
           x.id_tag
      FROM (SELECT h.id,
                   h.house,
                   h.country,
                   ht.id_tag,
                   CASE 
                     WHEN @id = h.id THEN @rownum := @rownum + 1
                     ELSE @rownum := 1
                   END AS rank,
                   @id := h.id,
                   @house_count := @house_count + 1 AS house_count
              FROM HOUSE h
         LEFT JOIN HOUSES_TAGS ht ON ht.id_house = h.id
              JOIN (SELECT @rownum := 0, @id := -1, @house_count := 0) r
          ORDER BY h.id, ht.id_tag) x
   WHERE x.house_count <= 10
     AND x.rank <= 4        
ORDER BY x.id, x.id_tag