Sql query with join

[AZUL]

Here are the structure of my tables

Comments
--------------
id auto_increment
date
Comment

comment_member
------------------
cm_id_comment
cm_id_member

User
---------------------
id auto_increment
username
.....

I wish I could retrieve the list of user reviews. I then test to make a request to retrieve my comments for user with id 1.

Here are the data in the database.

Comments
--------------------

1 2009-11-06 11:26:52 comments for user1
2 2009-11-05 11:27:11 comments for user2

comment_member
----------------------
1 1
2 2

User
-----------------
1 user1
2 user2

[MindSpace]

But what you have tried? And by the way, your table comment_member has no interest, directly put the id of the member in the table comment.

[AZUL]

If it has an interest because I have to differentiate the comments of a member or group.

If I tried that

SELECT * FROM 'comment'
INNER JOIN  'comment_member'
ON comment_member.cm_id_member= 1

But this returns me to the second user feedback too.

[MindSpace]

You did not do that with JOIN on the right field; the common field in both tables is the id of the comment. Your id_member = 1 will be in the WHERE clause.

[AZUL]

Like this then?

SELECT *
FROM 'comment'
INNER JOIN 'comment_member' ON comment_member.cm_id_member = comment.id
WHERE cm_id_member =1

[MindSpace]

Didn't you thought on this for atleast 30 seconds! Why would your id_member be equal to the id of the comment?

[AZUL]

Sorry for that but I am addicted to copy (CTRL + C) and paste (CTRL + V)

Anyways then the code would be like this:

SELECT *
FROM `comment`
INNER JOIN `comment_member` ON comment_member.cm_id_comment = comment.id
WHERE cm_id_member =1