JOIN 3 tables query

Question

ok, here we go...

I have 3 tables: posts, comments and members, i need to get title from posts, msg from comments and username from members. all tables has an identical ID to associate(?)

for example: i want to get these data from certain ID...

sorry for my bad english, isn't my language, and my very basic skill on MySQL (started today).

Edit: this is my schema:

posts:    |ID|title|
          ----------------
          |1 |post title|        (example data)

comments: |USERID|UID|msg|  (UID is same as posts.ID)
          -----------------
          |5     |1  |message|   (example data)    

members:  |USERID|username|
          -----------------
          |5     |myusername|    (example data)

when i make a query to ID 80 (eg.) will return the title, the messages associated to that post (UID) and the username associated to that comment.

for eg. if the post 80 have 5 comments shows, title of post and username of comment.

i think this is more clear. (no?)

Answer 1

Maybe something like this:

SELECT P.Title, C.Message, M.Username
FROM   Posts P
INNER JOIN Comments C ON P.PostID = C.PostID
INNER JOIN Memmbers M ON C.MemberID = M.MemberID
WHERE P.PostID = 123

Here it is with your schema (I think mine is better that's why I left it) :)

SELECT P.title, C.msg, M.username
FROM   posts P
INNER JOIN comments C ON P.ID = C.UID
INNER JOIN memmbers M ON C.USERID = M.USERID
WHERE P.ID = 80

The post title will be repeated in this case, but I believe this is what you are asking for.

Answer 2

Without seeing your schema I can only guess but I think it might look something like this:

SELECT p.title, c.msg, m.username
FROM Posts p
INNER JOIN Comments c ON (p.AssociateID = c.AssociateID)
INNER JOIN Members m ON (m.AssociateID = p.AssociateID)
WHERE p.AssociateID = 123

If your schema is different, adjust the SQL above to fit your table structure.

Answer 3

First of all your tables structure (singular table names) should look like something like this:

post: id title member_id

comment: id msg post_id member_id

member: id username

SELECT p.title, c.msg, m.username
FROM comment c
INNER JOIN post p ON p.id = c.post_id
INNER JOIN member m ON m.id = p.member_id
WHERE m.id = YOURUSERIDGOESHERE

Answer 4

You can also do something like:

SELECT P.Title, C.Message, M.Username
FROM   Posts P, Comments C, Members M 
WHERE P.ID = C.UID 
AND C.USERID = M.USERID 
AND P.PostID = 80

This will produce the same results as the JOIN several others suggested, but some people feel that the WHERE shows the relationships more clearly. This is just a matter of personal preference.