Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Unanswered: LIMIT in JOIN via emulated row numbers

    I have X Categories, Y Forums and Z threads.
    + Threads belong to one Forum
    + Forums belong to one Category

    I want to be able to select X Categories and
    to each Category select the first 3 Forums and
    to each Forum select the first 4 Threads.

    (numbers only as an example)

    I did this by emulating row_numbers via variables, as mysql does not support this out of the box.

    Unfotunately there is still something wrong with the row-numbers. Maybe someone could have a look what is wrong here.

    Query
    Code:
    SELECT
        CatRow,
        c_id,
        c_name,
        ForumRow,
        f_id,
        f_name,
        ThreadRow,
        t_id,
        t_title
    FROM (
        SELECT
            @cat_row    := IF(@prev_cat    = c.id, @cat_row+1, 1)   AS CatRow,
            @forum_row  := IF(@prev_forum  = f.id, @forum_row+1, 1) AS ForumRow,
            @thread_row	:= IF(@prev_thread = t.id, @thread_row+1, 1)AS ThreadRow,
            c.id        AS c_id,
            c.name      AS c_name,
            f.id        AS f_id,
            f.name      AS f_name,
            t.id        AS t_id,
            t.title     AS t_title,
            @prev_cat   := c.id,
            @prev_forum := f.id,
            @prev_thread:= t.id
        FROM (
            SELECT
                *
            FROM
                forum_categories c,
                (SELECT @cat_row := 1) AS x,
                (SELECT @prev_cat := '') AS y
            ORDER BY @cat_row
        ) AS c
    
        LEFT JOIN (
            SELECT
                *
            FROM
                forum_forums AS f,
                (SELECT @forum_row := 1) AS x,
                (SELECT @prev_forum := '') AS y
            ORDER BY @forum_row
        ) AS f ON (c.id = f.fk_forum_category_id )
    
        LEFT JOIN (
            SELECT
                *
            FROM
                forum_threads AS t,
                (SELECT @thread_row := 1) AS x,
                (SELECT @prev_thread := '') As y
            ORDER BY @thread_row
        ) AS t ON (f.id = t.fk_forum_forums_id )
    
        ORDER BY c.id ASC, f.id ASC, t.id ASC
    ) c
    Results:
    Code:
    CatRow	c_id	c_name	ForumRow f_id	f_name		ThreadRow	t_id	t_title
    4	1	General	4	2	Talk			1	42	talk
    5	1	General	5	2	Talk			1	43	Talk...
    6	1	General	6	2	Talk			1	44	locked thread
    7	1	General	7	2	Talk			1	45	closed thread
    3	1	General	3	2	Talk			1	48	:(:red::confuse::)
    1	1	General	1	2	Talk			1	50	gsfdgsdg
    2	1	General	2	2	Talk			1	51	asdasd
    9	1	General	2	5	Voting			1	47	some title
    8	1	General	1	5	Voting			1	49	sadfsad
    1	2	Support	1	3	Help			1	40	Hueeelefe
    2	2	Support	1	4	Features and Bugs	1	41	What is a bug?
    3	2	Support	1	7	Test			1	NULL	NULL	
    2	3	News	2	1	News			1	39	News by admin
    1	3	News	1	1	News			1	46	further news
    At the end I need to be able to specify:
    WHERE CatRow <= AND ForumRow <= 3 AND ThreadRow <= 4


    This is not yet possible due to the wrong row_numbers.
    Any ideas???

    I want the result to look like this:
    Code:
    CatRow	c_id	c_name	ForumRow f_id	f_name		ThreadRow	t_id	t_title
    1	1	General	1	2	Talk			1	42	talk
    1	1	General	1	2	Talk			2	43	Talk...
    1	1	General	1	2	Talk			3	44	locked thread
    1	1	General	1	2	Talk			4	45	closed thread
    1	1	General	1	2	Talk			5	48	:(:red::confuse::)
    1	1	General	1	2	Talk			6	50	gsfdgsdg
    1	1	General	1	2	Talk			7	51	asdasd
    1	1	General	2	5	Voting			1	47	some title
    1	1	General	2	5	Voting			2	49	sadfsad
    2	2	Support	1	3	Help			1	40	Hueeelefe
    2	2	Support	2	4	Features and Bugs	1	41	What is a bug?
    2	2	Support	3	7	Test			1	NULL	NULL	
    3	3	News	1	1	News			1	39	News by admin
    3	3	News	1	1	News			2	46	further news
    Last edited by pantu; 10-04-12 at 10:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •