Issue with JOIN sql statement

Question

I have two database tables I am attempting to join (home_comments and profile_img). What I am trying to attempt is matching the user_id from the home_comments database to the profile_img database, in order to get the profile image from the person who commented. The query I wrote is not broken, as it is producing results, however, the queried results are showing all of the images each user_id has in the profile_img database.

Whenever a person uploads a profile_img, I just add it to the database and then simply call for the last one within it to display the profile_img. So, I am looking for the same concept to be implemented. The user's last profile_img to show.

Whenever I add LIMIT 1 to the end of the query it only allows one comment to show, total. All I need is for the last (DESC) profile_img to show for each user, but still showing all of the comments. How could I accomplish this?

Initially I had the following query:

$select_comments_sql = "
    SELECT * 
    FROM home_comments
    ORDER BY id DESC
";

My attempt at the query

$select_comments_sql = "
    SELECT c. *,
    p.user_id, p.img
    FROM home_comments AS c
    INNER JOIN profile_img AS p
    WHERE c.user_id = p.user_id
    ORDER BY id DESC
";

Database setups:

home_comments

id, user_id, username, comment, date

profile_img structure:

id, user_id, img

Edit: I also just noticed that it is showing the same comment for each different photo in the database, so duplicated comments. Not sure if this has to do with the image issue.

SQL with query:

$select_comments_sql = "
    SELECT c.*, p.user_id, p.img
    FROM home_comments AS c
    INNER JOIN (SELECT max(ID) as ID, user_ID  
                FROM profile_img 
                GROUP BY user_ID) PI
      on PI.User_ID = C.USer_ID
    INNER JOIN   profile_Img p
      on PI.user_ID = P.User_ID 
     and PI.ID = P.ID
    ORDER BY C.id DESC
";
  if ($select_comments_stmt = $con->prepare($select_comments_sql)) {
        //$select_comments_stmt->bind_param("s", $user_id);
        $select_comments_stmt->execute();
        if (!$select_comments_stmt->errno) {
            //echo "error";
        }
        $select_comments_stmt->bind_result($comment_id, $comment_user_id, $comment_username, $home_comments, $comment_date, /*$commenter_id,*/ $commenter_user_id, $commenter_img);

        $comment_array = array();
        while ($select_comments_stmt->fetch()) {
            $comment_array[] = $comment_user_id;
            $comment_array[] = $comment_username;
            $comment_array[] = $home_comments;
            $comment_array[] = $comment_date;
            /*$comment_array[] = $commenter_id;*/
            $comment_array[] = $commenter_user_id;
            $comment_array[] = $commenter_img;
            $commenter_img = '<img class="home-profile-pic" src=" '.$commenter_img.'">';
            if ($home_comments === NULL) {
                echo 'No comments found.';
            } else {
                echo $commenter_img;
                echo $comment_username. "<br>";
                echo $home_comments. "<br><br><br>";
            }

Can we assume the highest profile_img.ID is the most recent image that you want to return? — xQbert
– xQbert, Commented Oct 20, 2016 at 14:41
I also just noticed that it is showing the same comment for each different photo in the database, so duplicated comments. Not sure if this has to do with the image issue. — Becky
– Becky, Commented Oct 20, 2016 at 14:43
You could join the tables intermediately with select max(id), user_id from profile_img group by user_id — K Richardson
– K Richardson, Commented Oct 20, 2016 at 14:48
@becky the same comment is because of the M-M relationship. You need a 1-M so that comments doesn't repeat. — xQbert
– xQbert, Commented Oct 20, 2016 at 14:52

xQbert · Accepted Answer · 2016-10-20 14:59:36Z

2

One approach would be to use an inline view to generate a set of data consisting of the max ID for each user's photo then use this set to limit the photos for all users..

In the below I generate a dataset called PI containing the max ID of each profile image for each user. Then this is joined back to profile_img entire set to act as a limit; thus only returning the most recent photo for a user. This in essence ensures you have a 1-M relationship between comments and Photo_image. The many-to-many was what was causing the problem.

SELECT c.*, p.user_id, p.img
FROM home_comments AS c
INNER JOIN (SELECT max(ID) as ID, user_ID  
            FROM profile_img 
            GROUP BY user_ID) PI
  on PI.User_ID = C.USer_ID
INNER JOIN   profile_Img p
  on PI.user_ID = P.User_ID 
 and PI.ID = P.ID
ORDER BY C.id DESC

edited Oct 20, 2016 at 14:59

answered Oct 20, 2016 at 14:47

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Becky Over a year ago

This seems to be breaking the query, but I cannot figure out where.

Becky Over a year ago

Wouldn't I need to select the id from the profile_img database as well? That would determine the most recent image.

Becky Over a year ago

I just added id to the p.user_id, p.img and it didn't seem to help.

xQbert Over a year ago

I can't see why this is breaking. the subquery returns ID, user_ID and is joined to Comments on user_ID. The second join is on profile_Img and joins to the subquery on User_ID and on the Profile_IMG.ID. What error are you getting? or could you post a sample tables/data to SQL FIDDLE (and post link back in your question) so that we could figure out what's wrong? Breaking the query isn't descriptive enough. Is PHP returning an error or is mySQL returning an error? if so what's the error?

Becky Over a year ago

Sure, one minute and I will send.

|

calcinai · Accepted Answer · 2016-10-20 14:57:44Z

1

If you're only running the query for one (or a few) users, the fastest way is probably to have a sub-select in your select instead of using a join, since you're not strictly 'joining' the data.

SELECT c. *,
(
    SELECT p.img 
    FROM profile_img AS p
    WHERE p.user_id = c.user_id
    ORDER BY p.id DESC
    LIMIT 1
) AS img
FROM home_comments AS c
WHERE c.user_id = x

This will avoid computing every user's most recent profile image when you're only ever fetching one (or a few), since having a nested query won't be able to join on an index.

edited Oct 20, 2016 at 14:57

answered Oct 20, 2016 at 14:52

calcinai

2,62716 silver badges25 bronze badges

10 Comments

Becky Over a year ago

Unfortunately, this is breaking the query.

calcinai Over a year ago

Did you copy it verbatim? I obviously can't test it but the where clause was just for example..

xQbert Over a year ago

@calcinai but wouldn't the inline select have to execute every time for each of the records returned? On a small data set I could see this being efficient but on a larger one, I see this taking longer. (guess I need to spend some time testing it)

calcinai Over a year ago

Exactly, that's why I made the comment about only using this method on a small dataset. I use small as a relative term - if the total users are only 100's there would be negligible difference.

calcinai Over a year ago

@Becky This query is working with your fiddle - sqlfiddle.com/#!9/0aea16/3

|

MonkeyZeus · Accepted Answer · 2016-10-20 15:30:15Z

0

I would imagine this should work but I have not tested it.

SELECT c.*, p.user_id, p.img, MAX(p.id) /* not sure if you need MAX(p.id) but try it with/without it here */
FROM home_comments c
LEFT OUTER JOIN profile_img p on p.user_id = c.user_id
WHERE p.id = MAX(p.id)
ORDER BY c.id DESC

answered Oct 20, 2016 at 15:30

MonkeyZeus

20.8k4 gold badges41 silver badges83 bronze badges

Collectives™ on Stack Overflow

Issue with JOIN sql statement

3 Answers 3

8 Comments

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related