[SOLVED] complex select - thursday teaser!

[jonniejoejonson]

I have mutliple tables.

The following sql attempts to select all the forums from the forum table.

It also selects all the categories for each selected forum, from the categroies table.

It also selects the most recent post in each selected category from the posts table.

It also selects the user info for the most recent poster.

 

 

$sql = "SELECT

a.*,

b.*,

c.maxPostId,

d.*,

e.*

FROM forums AS a

LEFT JOIN categories AS b ON b.forumId=a.forumId

LEFT JOIN (SELECT categoryId, MAX(postId) AS maxPostId FROM posts) AS c ON c.categoryId=b.categoryId

LEFT JOIN posts AS d ON c.maxPostId=d.postId

LEFT JOIN users AS e ON e.userId=d.userId

";

 

 

problem..

 

The sql works fine except it is only pulling the most recent post regardless of categoryId. It is also only selecting one post in total.

 

I hope you understand.. thanks to any responders.

[artacus]

Your subquery should be grouped by category_id. Most other db's wouldn't let you run that subquery for that reason.

 

Also, I would recommend giving your tables useful aliases and not a, b, c.

[jonniejoejonson]

artacus... thanks very much... it must be a wanderfull thing to be as brainy as you and the one they call keith... much appreciated.

[artacus]

it must be a wanderfull thing to be as brainy as you and the one they call keith.

 

LOL. On the contrary, suicide rates are MUCH higher in the gifted and genius populations. I guess lending some credibility to the phrase "ignorance is bliss." Besides, I'm sure it has more to do with 15 years of experience than braininess.