I have the following 3 tables:
users:id, username
folders:
id,fkuser(connects to user.id)
folder_comments:
id,fkuser(connects to user.id), fkfolder(connects to folder.id)
I want to get the commenter username(folder_comments.fkuser->user.id<->user.username
) AND and the username of the folder owner (folder_comments.fkfolder->folder.id<->folder.fkuser->user.id<->user.username
);
The query I have thoght of is partial:
SELECT `folder_comments`.*, `folder`.`fkuser` as folderuserid, `user`.`firstname` as usernameFROM `folder_comments`
left join `folder` on `folder_comments`.`fkfolder` = `folder`.`id`
left join `user` on `folder_comments`.`userid` = `user`.`id`
How can I get the folder's owner username?
edit :
I tried:
SELECT `folder_comments`.*, `folder`.`fkuser` as folderuserid, `user`.`firstname` as username, , `user`.`firstname` as folderownerFROM `folder_comments`
left join `folder` on `folder_comments`.`fkfolder` = `folder`.`id`
left join `user` on `folder_comments`.`userid` = `user`.`id`
left join `user` on `folder`.`fkuser` = `user`.`id`
but I get an error Not unique table/alias: 'user'
use user in join multiple times, something like this:
select folder.*, userfolder.username, comment.*, usercomment.username
from folders folder inner join
user userfolder on folder.fkuser = userfolder.id inner join
comments comment on comment.fkfolder = folder.id inner join
user usercomment on comment.fkuser = usercomment.id
when using the same table multiple times in a join it's important you use different alias
Not sure I get what's where in your schema
but you do it like this. This is get mum and dad's name from people
Select Child.Name,Mothers.Name, Father's.Name
From Child
Inner Join People Mothers On Child.MotherID = Mothers.PersonID
Inner Join People Fathers On Child.FatherID = Fathers.PersonID
So join to the table as many times as you need but give each one a different alias.