My schedule has a section for employee leave.

The structure of its table Vacation is as follows

Which is stored in the sender and Substitute section of a user ID

I want to connect to the user table and extract users whose numeric ID is in the table

This is the structure of my user table

Now I want to show in the section for the manager the list of all vacations in the vacation table along with the full name of the sender and successor

How can I do this?

I am using the following query but I have a problem with how to display it

SELECT * 
FROM vacation 
LEFT OUTER JOIN user ON vacation.sender=user.user_id AND vacation.Substitute=user.user_id

CodePudding user response：

Your query contains columns which could be present with the same name in more than one table you are referencing, hence the not unique error. It's best if you make the references explicit and/or use table aliases when joining. Additionally you mentioned user_id that does not exist.

Try this:

SELECT 
    v.id as vacationID, 
    u1.fullname as sender_Fullname, 
    u2.fullname as substitute_Fullname 
FROM vacation  as v
LEFT OUTER JOIN users as u1 ON v.sender=u1.id 
LEFT OUTER JOIN users as u2 ON v.Substitute=u2.id

See Demo in db<>fiddle

CodePudding user response：

SELECT * FROM n_vacation LEFT OUTER JOIN n_user ON n_vacation.sender=user.user_id LEFT OUTER JOIN n_user ON n_vacation.Substitute=user.user_id

The error I receive

QuerySELECT * FROM n_vacation LEFT OUTER JOIN n_user ON n_vacation.sender=user.user_id LEFT OUTER JOIN n_user ON n_vacation.Substitute=user.user_idfailed due toNot unique table/alias: 'n_user'