Jump to content

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


oopster

Recommended Posts

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!

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.

 

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.