Can someone help me with a mysql query please?
I have a question a开发者_运维知识库bout building a mysql query from multiple tables. The tables are following:
comments: comment_id, entry_id, user_id, comment, time..
users: user_id, user_name..
profile_photos: photo_id, user_id, photo_name
I want to get all comments
from comments table for a specific entry_id
, and for all of those users(who wrote comment), i want to get the photo_id(from profile_photos table, and user_name from users table).
For example 2 users user_id "1" and "2" wrote comment on "entry_id" "1". I want to get the comments data from comments table for those two users, as well as their user_name and photo_id.
If anyone can help me with the query, i will be really thankful.
SELECT
c.comment_id,
c.user_id,
c.comment,
c.time,
u.user_name,
p.photo_name
from
comments c
join users u
on c.user_id = u.user_id
left join profile_photos p
on u.user_id = p.user_id
where
c.entry_id = WhateverNumberYouWant
Add on whatever other columns from respective aliased tables ("c" = comment, "u" = users, "p" = photos)
The LEFT join is just in case there is no photo yet for a given user... it won't exclude any entries by such user.
SELECT comment, user_name, photo_name
FROM comments
JOIN users ON (comments.user_id = users.user_id)
JOIN profile_photos ON (users.user_id = profile_photos.user_id)
WHERE entry_id = {entry_id}
Try the following where entry_id is xxx, I included the user_name too
select comments.comment,profile_photos.photo_id,users.user_name
from comments,users,profile_photos
where
comments.entry_id = xxx and
users.user_id = comments.user_id and
users.user_id = profile_photos.user_id;
精彩评论