Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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 |
+----------+------------+-----------+---------------------+
share|improve this answer
    
Nice and thanks I'll try tomorrow from office –  ReynierPM May 10 '12 at 21:38
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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