MYSQL - 从多列表查询中的一列中选择两次

Question

我正在尝试将联系人（T.ContactId）查找添加到现有查询中。该查询使用客户端ID从客户端表中获取客户端。我现在希望添加T.ContactId以获取客户表中的另一个名称。在下面的脚本我已经添加了“T.ContactId”的选择，但我不知道如何从那里继续

  select T.Id Tid,Transdate,Quantity Unit,Amount Rate,Discount,T.Comment Comment,T.CmntToInvoice ConInv,T.JobNum JobNum,T.PayNum PayNum,T.ContactId,clients.Id `Id`,`Client`,Cell,Email,Yard,Horse,TransType `Transaction`,PayTypeId,Credit 
     from 
      transactions T,clients,yards,horses,transtypes 
     where 
      Pending = 'N' and 
      T.TransTypeId = transtypes.Id and 
      T.ClientId = clients.Id and 
      T.HorseId = horses.Id and 
      T.YardId = yards.Id and 
      Transdate between '2014-09-08' and '2016-07-08' and 
      T.JobNum = 0 
     order by 
      clients.Id,Transdate asc 

Answer 1

你应该改变你的隐加入到明确连接，并添加第二个连接获取客户端对于t.contactid ID等试试这个

select T.Id Tid,Transdate,Quantity Unit,Amount Rate,Discount,T.Comment Comment,T.CmntToInvoice ConInv,T.JobNum JobNum,T.PayNum PayNum, 
      T.ContactId,c1.id as 'ccid',c1.client as 'ContactCLient', 
      clients.Id `Id`,`Client`,Cell,Email,Yard,Horse,TransType `Transaction`,PayTypeId,Credit 
from transactions T 
join   clients on T.ClientId = clients.Id 
join  yards on T.YardId = yards.Id 
join   horse on T.HorseId = horses.Id 
join   transtypes on T.TransTypeId = transtypes.Id 
left outer join clients c1 on c1.id = t.contactid 
where  Pending = 'N' and 
      Transdate between '2014-09-08' and '2016-07-08' and 
      T.JobNum = 0 
order by clients.Id,Transdate asc 

我没有测试过这一点，但如果你能发布样本数据和预期结果的话，我会很乐意重新审视。