_动力学知识库

问题描述:

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 username
FROM `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 folderowner
FROM `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.