Solved ZNOTE AAC, help with SQL query (JOIN thing)

[zbizu]

I want to fetch 10 newest posts/threads in a single query to return a table as in this picture:

it should end this way:

ORDER BY `created` DESC LIMIT 10;

Tried with join/inner join, but I keep getting errors or weird results.
Could anyone help?

 

[Ninja]

Perhaps something along the lines of:

SELECT `zfp`.`id` AS `post_id`, `zfp`.`thread_id`, `zfp`.`created` FROM `znote_forum_posts` AS `zfp` LEFT JOIN `znote_forum_threads` AS `zft` ON `zfp`.`thread_id` = `zft`.`id` ORDER BY `created` DESC LIMIT 10;

 

[zbizu]

Got weird result again. Nevermind, I will just use PHP array for it.

 

[Znote]

This was really challenging in mysql, but think I got it working:

( SELECT `t`.`title` AS `thread_title`,
  `t`.`id` AS `thread_id`,
  `p`.`id` AS `post_id`,
  IFNULL(`p`.`created`, `t`.`created`) AS `created`,
  IFNULL(`p`.`player_name`, `t`.`player_name`) AS `player_name`,
  IFNULL(`p`.`player_id`, `t`.`player_id`) AS `player_id`
  FROM `znote_forum_threads` AS `t`
  LEFT JOIN `znote_forum_posts` AS `p` ON `t`.`id` = `p`.`thread_id`
) union all (
  SELECT `t`.`title` AS `thread_title`,
  `t`.`id` AS `thread_id`,
  `p`.`id` AS `post_id`,
  IFNULL(`p`.`created`, `t`.`created`) AS `created`,
  IFNULL(`p`.`player_name`, `t`.`player_name`) AS `player_name`,
  IFNULL(`p`.`player_id`, `t`.`player_id`) AS `player_id`
  FROM `znote_forum_threads` AS `t`
  RIGHT JOIN `znote_forum_posts` AS `p` ON `t`.`id` = `p`.`thread_id`
  WHERE `p`.`id` = NULL
) ORDER BY `created` DESC LIMIT 10;

I had to merge 2 queries with union, and then exclude duplicate rows. Now threads + posts are grouped together and ordered by created.
The threads don't have post id, so it will return null. But the other things will work, and it will priority post columns in favor of thread columns (etc a posts created value instead of a thread created value).

 

[sampleizsample]

znote is the best, love u from dutch heart

 

[zbizu]

Awesome! I will test it this evening (11 am now here). Thank you.

 

[Znote]

@zbizu Results? I am quite curious to know the results.

 

[zbizu]

@Znote