CODEWITHSUNDEEP
mysql
Dave
Dave

Reputation: 39

MYSQL - Select twice from one column in multi table query

I am trying to add a contact person (T.ContactId) lookup to an existing query. The query uses a client id to get the client from the clients table. I now wish to add T.ContactId to get another name from the clients table. In the script below I have already added 'T.ContactId' to the select but I dont know how to continue from there

            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

Upvotes: 0

Views: 49

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

You should change your implicit joins to explicit joins and add a second join to get the client id etc for t.contactid Try this

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

I haven't tested this but if you can publish sample data and expected results then I would be happy to revisit.

Upvotes: 1

Related Questions