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
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