Select data from 2 tables in mySQL, join it

Question

I need to select data from 2 tables in my DB - comments and users. This is the structure of these tables:

comments:

commentID | commentDateAndTime | imageID | userID | comment

users:

userID | username

I need to select all the data in both tables, join it on userID, where imageID should be equal to $imageID. I tried the following SELECT statement:

    return json_encode(select("
    SELECT
     commentDateAndTime,
     imageID,
     userID,
     COMMENT,
     username
   FROM users
    JOIN comments
    ON comments.userID = users.userID
   WHERE comments.imageID = $imageID
   GROUP BY comments.comment   
"));

But I didn't receive any data.

Any advice, please?

Why do you need the group by? doesn't look like you're using it. — Alex Ackerman
– Alex Ackerman, Commented Apr 9, 2012 at 8:28
@diEcho Yup, that worked. I also changed SELECT commentDateAndTime, imageID, userID, comment, username to SELECT * Now worked fine! Thank you! — Igal
– Igal, Commented Apr 9, 2012 at 8:35
@AlexAckerman: Throw that in an answer so that lgal can close this question out. — user1228
– user1228, Commented Apr 9, 2012 at 12:34

Muhammad Raheel · Accepted Answer · 2012-04-09 08:39:29Z

4

Why are you using group by if you need all the result.

SELECT users.* , comments.*
FROM users JOIN comments ON comments.userID = users.userID 
WHERE comments.imageID = $imageID

answered Apr 9, 2012 at 8:39

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

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

Comments

MrFusion · Accepted Answer · 2012-04-09 08:49:50Z

2

MySQL does auto-joins (from this page: "The FROM [...] clause indicates the table or tables from which to retrieve rows. If you name more than one table, you are performing a join").

Also, since there are two userID columns you need to be specific of which one by preceding the table name.

So your query could be like this:

SELECT commentDateAndTime, imageID, users.userID AS userID, comment, username
FROM comments, users
WHERE comments.userID = users.userID AND comments.imageID = $imageID

answered Apr 9, 2012 at 8:49

MrFusion

9117 silver badges16 bronze badges

Collectives™ on Stack Overflow

Select data from 2 tables in mySQL, join it

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related