[SOLVED] joining posts,categorys,tie tables with a limit

[oopster]

Hello

 

This is my first post, and I hope you all bear with me.

 

The point of the problem is, that i have to get all categorys and 10 latest posts under each category.

I have 3 tables: categorys,posts,tie. Something like this:

 

CREATE TABLE tie(
  id_tie int NOT NULL auto_increment,
  post_id int NOT NULL,
  category_id int NOT NULL,
  PRIMARY KEY (id_tie)
) TYPE=INNODB;

CREATE TABLE categorys (
  id_category int(3) NOT NULL auto_increment,
  description varchar(50) NOT NULL,
  PRIMARY KEY (id_categoryint),
) TYPE=INNODB;

CREATE TABLE posts(
  id_post int(3) NOT NULL auto_increment,
  title varchar(30) NOT NULL,
  content text NOT NULL,
  PRIMARY KEY (id_post)
) TYPE=INNODB;

 

And ou yes, I'd like to get it all with a single query - query inside a query.

So this is what i have right now:

 

-- query : to get all the categorys and under them 10 latest posts under that category
SELECT category.description,tie.category_id,tie.post_id FROM tie,category, 
(SELECT * FROM posts LIMIT 10) as nl WHERE 
category.id_category = tie.category_id AND nl.id_post = tie.post_id

 

Maybe i should use join, but i'm not sure, i'v never done a query inside a query with a limit.

Hope someone can help me with this.

 

THank you in advance!

[fenway]

That's actually the only way to do it... you can't use LIMIT in a JOIN any other way.

[mezise]

I know one way but it's a little tricky:

 

SELECT
    c.description
    , t.category_id
    , t.post_id
    , t2.i
FROM tie AS t
INNER JOIN (

    SELECT 
        t.id_tie
        , IF(t.category_id != @i_cat, @i := 1, @i := @i + 1) AS i
        , @i_cat := t.category_id c
    FROM tie t
    , (SELECT @i := 0, @i_cat := 0) AS INIT_VARIABLES
    ORDER BY 
        t.category_id, t.post_id DESC
    
    ) AS t2 ON t2.id_tie = t.id_tie
INNER JOIN categorys c ON c.id_category = t.category_id
HAVING
    t2.i <= 10
;

 

Your LIMIT number is moved into the HAVING clause.

 

[fenway]

That's not any better than a derived table.

[oopster]

Hello

 

Good to know that i'm on a right track. The little tricky query is way-way to complex for me, but thank you!