1

I've this query:

 SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id,
  `default_wall_posts_comments`.`comments` as comments, 
  UNIX_TIMESTAMP() - default_wall_posts_comments.date_created AS pass_time_comment, 
  `default_profiles`.*, `default_wall_posts`.*, 
  UNIX_TIMESTAMP() - default_wall_posts.date_created AS pass_time
 FROM (`default_wall_posts`)
 LEFT JOIN `default_profiles` 
    ON `default_profiles`.`user_id` = `default_wall_posts`.`userid`
 LEFT JOIN `default_wall_posts_comments` 
    ON `default_wall_posts`.`p_id` = `default_wall_posts_comments`.`post_id`
 WHERE `default_wall_posts`.`userid` =  '1'
 ORDER BY `default_wall_posts`.`date_created` DESC LIMIT 7

But results for the main table which is default_wall_posts are repeated for each default_wall_posts_comments.

I'm trying to get all comments for one default_wall_posts. Can be this query optimized in some way?

2

instead of left join try inner join, take a look to this graphic, and if you want to paginate you should use: limit start_INT, number_int_per_result

Example

jcho360> select * from actor limit 0,5;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
5 rows in set (0.00 sec)

jcho360> select * from actor limit 5,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        6 | BETTE      | NICHOLSON | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL    | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK     | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)

jcho360> select * from actor limit 10,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       11 | ZERO       | CAGE      | 2006-02-15 04:34:33 |
|       12 | KARL       | BERRY     | 2006-02-15 04:34:33 |
|       13 | UMA        | WOOD      | 2006-02-15 04:34:33 |
|       14 | VIVIEN     | BERGEN    | 2006-02-15 04:34:33 |
|       15 | CUBA       | OLIVIER   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
  • Nice and thanks I'll try tomorrow from office – ReynierPM May 10 '12 at 21:38
3

That's how joins work! Don't select the wall posts in the same query if you just want the comments.

  • Hmmm what about if I want to use that query for paginate results? See the LIMIT ... Should work fine? – ReynierPM May 10 '12 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.