Prashant Yadav Prashant Yadav - 1 year ago 64
MySQL Question

How to get values from table

I am facing sql query issue.
Following is the table structure

We have table tbllogin where we are maintaining all type of users like user,admin and vendor columns like
userId(pk), name, email.

we have another table tbltransaction where we are maintaining transaction of users column like

id(pk) userId(fk->tbllogin) vendorId(fk->tbllogin)

I want to show name as userName,name as vendorName ,email as vendorEmail from tbllogin on the basis of userId and vendorId of tbltransaction

Here two columns in tbltransaction referring two same table tbllogin

Please help me to solve this query issue.

I am also attaching images of sample table structure for more clarity

Answer Source

This should work:

 select Id, as userName, as vendorName, as vendorEmail
 from tbltransaction as t
 join tbllogin as lu on (lu.Id = t.userId)
 join tbllogin as lv on (lv.Id = t.vendorId)