Jump to content

Need help with JOINS


Skewled
Go to solution Solved by Psycho,

Recommended Posts

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

 

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
        "
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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 by Psycho
Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

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!!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.