SQL Select with 2 foreign key referencing on the same table

i have a table that contains a user: ID,Name,UserName and a table that contains an event:ID,AskerID,DoerID,EventName I need to get the name of the Asker using AskerID but I already have a left outer join that get the doer one

I'm not very good with sql ...a little help is always welcome

Answers


You can like one table to other tables based on different ChildKeys (AskerID, DoerID).

Select
 a.Name as Asker
 , d.Name as Doer
 , e.EventName
from event as e
left outer join user as a
on e.AskerID = a.ID
left outer join user as d
on e.DoerID = d.ID

Assuming the table with the user is called Users and the table with the event is called Events:

select   e.ID
         e.AskerID
         a.Name as AskerName
         e.DoerID
         d.Name as DoerName
         e.EventName
from     Events e
   left join Users a
          on e.AskerID = a.ID
   left join Users d
          on e.DoerID = d.ID

You can left join (or inner join) to the same table more than once: you just have to give one or both of them aliases. That's what the a, e, and d are. You may also want to rename some of the fields: that's why I renamed a.Name to AskerName and d.Name to DoerName.


It goes like this,

SELECT E.*, U1.ID AS DOERID, U2.ID AS ASKERID
FROM AS EVENT AS E
LEFT JOIN USER AS U1
ON E.DOERID = U1.ID
LEFT JOIN USER AS U2
ON E.ASKERID = U2.ID

Need Your Help

Const correctness with reference_wrapper

c++ c++11 reference-wrapper

In various situations I have a collection (e.g. vector) of objects that needs to be processed by a number of functions. Some of the functions need to modify the objects while others don't. The obje...

Trigger the bundle inclusion from partial or editor templates in ASP.NET MVC 4

asp.net .net asp.net-mvc-4 bundle asp.net-optimization

I'm building a framework on top of ASP.NET MVC 4. I like the new bundling feature in MVC4 but I'm running into an issue and I'm not sure if the new bundling features address this scenario.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.