oopster Posted May 15, 2008 Share Posted May 15, 2008 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! Link to comment https://forums.phpfreaks.com/topic/105761-solved-joining-postscategorystie-tables-with-a-limit/ Share on other sites More sharing options...
fenway Posted May 15, 2008 Share Posted May 15, 2008 That's actually the only way to do it... you can't use LIMIT in a JOIN any other way. Link to comment https://forums.phpfreaks.com/topic/105761-solved-joining-postscategorystie-tables-with-a-limit/#findComment-542155 Share on other sites More sharing options...
mezise Posted May 16, 2008 Share Posted May 16, 2008 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. Link to comment https://forums.phpfreaks.com/topic/105761-solved-joining-postscategorystie-tables-with-a-limit/#findComment-542514 Share on other sites More sharing options...
fenway Posted May 16, 2008 Share Posted May 16, 2008 That's not any better than a derived table. Link to comment https://forums.phpfreaks.com/topic/105761-solved-joining-postscategorystie-tables-with-a-limit/#findComment-543022 Share on other sites More sharing options...
oopster Posted May 17, 2008 Author Share Posted May 17, 2008 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! Link to comment https://forums.phpfreaks.com/topic/105761-solved-joining-postscategorystie-tables-with-a-limit/#findComment-543463 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.