如何将'Hosts'添加为单独的行，尽管信息与用户显示在同一表中

Question

在我们的数据库结构中，我们有用户。在此表中，说明了用户是否为主机。这是通过字段"IsHost"（布尔值）实现的。我想运行一个查询，在一行（访客）和另一行中显示不是主机的用户，该行说明了访问的主机名称。

我将复制查询的"选择"和"从"部分以帮助您。

SELECT  [User].FirstName + [User].LastName AS 'Visitors Name',
        User.FirstName + User.LastName AS 'Host Name',
        Company.Name AS 'Company Name',
        VUT.AutoCheckoutTime, 
        VUD.ExpectedTime AS 'Expected Time on Site'
FROM  [User] 
INNER JOIN VisitUser ON [User].UserId = VisitUser.UserId 
INNER JOIN Visit ON VisitUser.VisitId = Visit.VisitId 
INNER JOIN VisitUserType VUT ON [User].DefaultVisitUserTypeId = VUT.VisitUserTypeId 
                            AND VisitUser.VisitUserTypeId = VUT.VisitUserTypeId 
INNER JOIN VisitUserDate VUD ON VisitUser.VisitUserId = VUD.VisitUserId 
LEFT OUTER JOIN Company ON [User].CompanyId = Company.CompanyId

Answer 1

好吧，记得怎么做。添加
hostuser.FirstName + ' ' + hostuser.lastname [Main Host],选择和

Visituser hostvisituser on Visit.VisitId = hostvisituser.VisitId 
                        and hostvisituser.IsMainHost = 1             
LEFT JOIN [User] hostuser on hostvisituser.UserId = hostuser.UserId 

到从。如果有人看过，干杯