Need help with JOINS

[Skewled]

I have the following query:

"SELECT a.blog_id, a.dateposted, a.content, a.title, a.published, b.login,
 b.img, c.username, c.comment
        FROM blog a
        JOIN user b
            ON a.user_id = b.user_id
        RIGHT JOIN blog_comments c
            ON a.blog_id = c.blog_id
        WHERE a.published = 1
        GROUP BY a.dateposted DESC
        LIMIT 5
        "

I have gotten the proper data but I can't figure out how to get the all of the comments for each blog post, currently it will only join the rows and add the first comment, and leave the remaining comments out.

 

Tables are:

 

blog

 

blog_id - Primary | dateposted | content | title | published

 

user

 

user_id - Primary | login | img 

 

blog_comments

 

comment_id - Primary | blog_id - FKey | comment

 

 

I appreciate the assistance, I'm new to JOINS and my Google reading has allowed me to compile this query so far but it's getting over my head lol.

[Psycho]

Your GROUP BY is "grouping", i.e. collapsing, the records. That needs to be taken out. Also, the JOIN on the comments should be a LEFT JOIN. This assumes you want all of the blog posts - even if there are no comments. Lastly, you have a LIMIT with will only allow 5 records to be returned. Because of the JOINs you may get five records all for the same blog post with different comments. Or you could get five blog posts with one comment each. Unless you can explain what you were trying to achieve with that limit, it's difficult to know how it needs to change.

 

You reference a field called username in the SELECT clause that you don't mention as being in the comments table and it doesn't make sense being in that table to me.

 

I removed the aliases from the first two tables. The point of using aliases is to make it easier to read the query. When you use something like 'a' which has no correlation to the table name it has the opposite effect. I wouldn't use an alias on a table that is only four characters long anyway.

 

 

SELECT blog.blog_id, blog.dateposted, blog.content, blog.title, blog.published,
       user.login, user.img,
       bc.username, bc.comment
FROM blog
JOIN user
  ON blog.user_id = user.user_id
LEFT JOIN blog_comments bc
  ON blog.blog_id = bc.blog_id
WHERE blog.published = 1
ORDER BY blog.blog_id DESC

[Skewled]

Psycho,

 

Thank you for the very clear explanation and what I'm trying to achieve by the limit was to have 5 of the main blog posts to be pulled, and than all of the comments for those blogs to be pulled to be displayed using jQuery. The issue with the query is it's posting each blog multiple times for each comment posted, which is why I used the Group By but I clearly misunderstood the use. I will use aliases differently now as well.

 

I placed the username in the comment table to make it easy to pull latest comments using 1 query without a JOIN.

 

Is it possible to achieve what I need in a single query and if so where is my logic failing me?

 

Thank You!

[jazzman1]

 

The issue with the query is it's posting each blog multiple times for each comment posted, which is why I used the Group By

 

Yes, that's correct. When the data has been pulled down from DB you need to use an application language technique to display the content in an order you want, or you could use a mysql group_concate function to concatenate all comments to each group of blog and again you need to use the application language technique.  

"SELECT a.blog_id, a.dateposted, a.content, a.title, a.published, b.login,
 b.img, c.username, GROUP_CONCAT(c.comment)
        FROM blog a
        JOIN user b
            ON a.user_id = b.user_id
        RIGHT JOIN blog_comments c
            ON a.blog_id = c.blog_id
        WHERE a.published = 1
        GROUP BY a.dateposted DESC
        LIMIT 5
        "

[Barand]

I wouldn't advocate GROUP_CONCAT for fields like comments which can be fairly lengthy. GROUP_CONCAT has a 1024 character limit and you could easily get unknown truncation of the output with long data fields.

[jazzman1]

I completely forgot that it has a 1024 character limit by default

[Skewled]

Thank you each for your input, I also read about GROUP_CONCAT and discovered the limit so I choose to not use it as a viable solution. 

 

Jazzman, you mention pulling the data and then using an application language technique, I've never heard it put that way but are you referring to having PHP(or server side language) parse the data and re-organize it?

 

 I'm thinking the simplistic approach would have to be using 2 queries and tacking the comments to each blog_id?

 

Again, thank each of you for your input and wisdom thus far.

[jazzman1]

When you send the query above and fetch the data from db server, do print_r() to see how the structure of data is represented. 

Yes,your application language is php, but could be perl, python,bash and so on...

[Psycho]

When you are querying data with JOINs where there could be multiple records JOINed on a single record you get results such as this:

blog_id | blog_text | comment_id | comment_text
-------------------------------------------------
 2        Blog2Text    4           comment4text
 2        Blog2Text    7           comment7text
 2        Blog2Text    8           comment8text
 5        Blog5Text    9           comment9text
 5        Blog5Text    11          comment11text

There are two unique blogs. The first has 3 comments and the second has two comments. This is normal. Now if you wanted to display each blog one time along with the associated comments you do this in the code. Example

<?php
 
$blogID = false; //Usa a flag to check when the parent record changes
while($row = mysql_fetch_assoc($result))
{
    //Check if this is a new blog
    //This is skipped for a record if the blog is same as the last iteration
    if($blogID != $row['blog_id'])
    {
        //This is a new blog, let's display it
        $blogID = $row['blog_id'];
        echo "<br><b>" . $row['blog_text'] . "</b><br>";
    }
 
    //Display the comment
    echo "Comment: " $row['blog_text'] . "<br>";
}
 
?>

The output of the above should look something like this:

 

Blog2Text

Comment: comment4text

Comment: comment7text

Comment: comment8text

 

Blog5Text

Comment: comment9text

Comment: comment11text

Edited April 11, 2014 by Psycho

[Psycho]

As for only getting the first five blogs with all of their comments, you would likely need to use a subquery with the limit. [Note the second to last line int he query below]

SELECT blog.blog_id, blog.dateposted, blog.content, blog.title, blog.published,
       user.login, user.img,
       bc.username, bc.comment
FROM blog
JOIN user
  ON blog.user_id = user.user_id
LEFT JOIN blog_comments bc
  ON blog.blog_id = bc.blog_id
WHERE blog.published = 1
  AND blog.blog_id IN (SELECT blog_id FROM blog ORDER BY dateposted LIMIT 5) as L
ORDER BY blog.blog_id DESC

Edited April 11, 2014 by Psycho

[Skewled]

Psycho, Jazzman, and Barand

 

 Thank you each for your input, I now understand what I was doing wrong and what I needed to do for a solution. The version of MySQL I'm using won't allow the Limit subquery you created, I will have to do something for limiting or breaking the data into pages but that would be on the PHP end. The example you posted really helped me visualize what Jazzman was talking about and really gave me a huge leg up on completing a personal project.

 

 Thanks!!