#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    77
    Rep Power
    11

    Join and group 2 tables


    So now i got help how to join 2 tables and get out some results, the problem is that i now have 2 tables that i want to combine and group.
    I thought I would be able to do this, but I was wrong. So now i ask you again if anyone could help me.

    These are the tables:

    Code:
    mysql> select * from f_posts;
    
    +---------+----------+----------+-----------+------------+-----------+---------------+
    | post_id | topic_id | forum_id | poster_id | post_time  | poster_ip | post_username |
    +---------+----------+----------+-----------+------------+-----------+---------------+
    |       1 |        1 |        1 |         2 |  972086460 | 7F000001  | NULL	     |
    |       2 |        1 |        1 |        -1 | 1143023214 | 7f000001  | sas	     |
    |       3 |        2 |        1 |         2 | 1143121598 | 7f000001  |	pelle	     |  
    |       4 |        2 |        1 |         2 | 1143121620 | 7f000001  |	mikael       | 
    |       5 |        1 |        1 |         2 | 1143127553 | 7f000001  |	Nicke	     |
    +---------+----------+----------+-----------+------------+-----------+---------------+
    
    
    mysql> select * from phpBB_posts_text;
    +---------+------------+--------------+--------------+
    | post_id | bbcode_uid | post_subject | post_text    |
    +---------+------------+--------------+--------------+
    |       1 |            | NULL         | example post |
    |       2 | 21f3d5764a | sadsda       | sdadsasd     |
    |       3 | f67c269f46 | hfhfhg       | thghg        |
    |       4 | f67c269f46 |              | ghghhg	     |
    |       5 | 75a8f99469 | fhdfbd       | bfdfbfdf     |
    +---------+------------+--------------+--------------+
    I'm not really sure how i should type this as one SQL-statement:

    select f_posts_text.post_subject, Where f_posts_text.post_id = f_posts.post_id and With the lowest
    f_posts_text.post_id for the group that has the same f_posts.forum_id

    select f_posts.post_username ,With the lowest f_posts.post_id
    where f_posts.forum_id = $id for the group that has the same f_posts.forum_id

    select f_posts.post_username, With the highest f_posts.post_id
    where f_posts.forum_id = $id for the group that has the same f_posts.forum_id

    Then i also would like to count all the data grouped by post_id.topic_id -1 And this will result in all the answers that the post got.


    The result from the SQL-statment where f_posts.forum_id = 1 should be:
    Code:
    +---------+---------------+--------------+------------------+---------------+
    | post_id | post_username | post_subject | post_username2   | answers   |
    +---------+---------------+--------------+------------------+---------------+
    |       1 | NULL          | NULL         | Nicke	    | 2		    |
    |       3 | pelle         | hfhfhg       | mikael           | 1		    |
    +---------+---------------+--------------+------------------+---------------+
    
    
    *post_username2 is the latest post into the forum


    Regards,
    Mikael
    Last edited by mikael_b; March 23rd, 2006 at 11:57 AM. Reason: misleading title
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    77
    Rep Power
    11
    Is there anyone that knows how to do this? If i have to explain what i want to achieve in another way, please tell me.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    77
    Rep Power
    11
    Ok, so some of the work is done. Since nobody was able to help me I tried to do it by my self, but i didn't really succeed.

    This is what i have done so far:

    Code:
    	$sql = "select f_posts.post_id
    				 , f_posts.forum_id 
    				 , f_posts.topic_id
    				 , f_posts.poster_id
    				 , f_posts.post_time
    				 , f_posts.poster_ip
    				 , f_posts.post_username
    				 , f_posts.post_edit_time
    				 , f_posts.post_edit_count
    				 , f_posts_text.post_subject
    			from f_posts_text
    				left outer 
    					 join f_posts 
    						on f_posts.post_id = f_posts_text.post_id
    					 WHERE f_posts.forum_id = ".$id."";
    And the array it prints is:

    Array (

    [0] => Array ( [post_id] => 3 [forum_id] => 4 [topic_id] => 1 [poster_id] => 0 [post_time] => 0 [poster_ip] => [post_username] => [post_edit_time] => [post_edit_count] => 0 [post_subject] => testaren )

    [1] => Array ( [post_id] => 4 [forum_id] => 4 [topic_id] => 2 [poster_id] => 0 [post_time] => 0 [poster_ip] => [post_username] => [post_edit_time] => [post_edit_count] => 0 [post_subject] => testaren )

    )
    So now i have joined my to tables and they are working together the way they should. The question is now how i could select "post_username2". It should be f_post.post_username with the highest post_id. I also would like to group all the data in f_posts with the same f_posts.topic_id and count the result.

    I hope someone could help me with this
  6. #4
  7. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    Did Rudy's help in your other thread not help you on this?

    also a thought, when showing output, don't print out an array where people have to read through the pointers etc. show just the rows that they would make up.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    77
    Rep Power
    11
    Originally Posted by Guelphdad
    Did Rudy's help in your other thread not help you on this?

    also a thought, when showing output, don't print out an array where people have to read through the pointers etc. show just the rows that they would make up.
    It helped me, but i can't figure it out how i should group and count the answers that a thread got.

    Ok, sorry for posting an array, want happen again.

    I searched at the forum and tried this solution:

    Code:
    	$sql = "select f_posts.post_id
    				 , f_posts.forum_id 
    				 , f_posts.topic_id
    				 , f_posts.poster_id
    				 , f_posts.post_time
    				 , f_posts.poster_ip
    				 , f_posts.post_username
    				 , f_posts.post_edit_time
    				 , f_posts.post_edit_count
    				 , f_posts_text.post_subject
    				 ,sum(case when f_posts.topic_id = 1 then 1 else 0 end ) AS answers
    			from f_posts_text
    				left outer 
    					 join f_posts 
    						on f_posts.post_id = f_posts_text.post_id
    					 WHERE f_posts.forum_id = ".$id." 
    					group by f_posts.topic_id";
    But there are 2 problems with this one, first of all I don't think it is the best way and second of all it will only return one row.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    77
    Rep Power
    11
    Sorry for all the trouble and unclear posts I have made so far, I will try to sum it up.

    This is my SQL-statement right now:
    Code:
    	$sql = "select f_posts.post_id
    				 , f_posts.forum_id 
    				 , f_posts.topic_id
    				 , f_posts.poster_id
    				 , f_posts.post_time
    				 , f_posts.poster_ip
    				 , f_posts.post_username
    				 , f_posts.post_edit_time
    				 , f_posts.post_edit_count
    				 , f_posts_text.post_subject
    				 ,sum(case when f_posts.topic_id = 1 then 1 else 0 end ) AS post_answers
    			from f_posts_text
    				left outer 
    					 join f_posts 
    						on f_posts.post_id = f_posts_text.post_id
    					 WHERE f_posts.forum_id = ".$id." 
    					group by f_posts.topic_id";
    The problem with this one is that it don't returns what I would like it to do, and that "sum(case when f_posts.topic_id = 1 then 1 else 0 end ) AS post_answers" isn't dynamic.

    I would like it to group all the post with the same f_posts.forum_id and f_posts.topic_id, then return f_posts.poster_id for the field with the lowest and highest f_posts.post_id. I also would like to count this group the fields in this group.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    77
    Rep Power
    11
    Ok, so i solved the count problem:

    Code:
    	$sql = "select f_posts.post_id
    				 , f_posts.forum_id 
    				 , f_posts.topic_id
    				 , f_posts.poster_id
    				 , f_posts.post_time
    				 , f_posts.poster_ip
    				 , f_posts.post_username
    				 , f_posts.post_edit_time
    				 , f_posts.post_edit_count
    				 , f_posts_text.post_subject
    			     , COUNT(f_posts.poster_id)-1 AS post_answers
    			from f_posts_text
    				left outer 
    					 join f_posts 
    						on f_posts.post_id = f_posts_text.post_id
    					 WHERE f_posts.forum_id = ".$id." 
    					group by f_posts.topic_id";
    This will return the post_answers that i wanted, but the still I would like it to group all the post with the same f_posts.forum_id and f_posts.topic_id, then return f_posts.post_id for the field with the lowest and highest f_posts.post_id.

    Because what i would like it to return is f_posts.poster_id, f_posts.poster_ip, f_posts.post_username and f_posts_text.post_subject with the lowest f_posts.post_id inside each group.
    And I would like it to return f_posts.poster_id, f_posts.poster_ip, f_posts.post_username with the highest f_posts.post_id inside ache group.

IMN logo majestic logo threadwatch logo seochat tools logo