MySQL SQL command to query from one table with three same columns to another table
Sorry but I am not sure how to ask this question but I am working on a help desk application where I have tickets being created in one table. I also have another table that stores the users. My problem is with the tickets table, I have listed the user that created the ticket, the tech who will solve the ticket and a user that over sees the ticket. All three users reference the users table. So how do I can I query the tickets table and get all three users that reference the same table storing the users?
1) Ticketnumber 2) EnteredBy User 100 3) Issue 4) FixedBy User 102 5) FixedByNotes 6) ResponsilbeUser User 103
1) UserID 2) UserName
What I can do now is something like this:
Select Ticketnumber, EnteredBy, Issue, UserName FROM Tickets INNER JOIN Users ON Tickets.EnteredBy = Users.UserID
You can extend current query to somewhat as follows:
Select Ticketnumber, Issue, Reporter.UserName, Developer.UserName, Manager.UserName FROM Tickets INNER JOIN Users AS Reporter ON Tickets.EnteredBy = Reporter.UserID INNER JOIN Users AS Developer ON Tickets.FixedBy = Developer.UserID INNER JOIN Users AS Manager ON Tickets.ResponsibleUser = Manager.UserID
You need alias for joint tables if you want to get all names:
Select Ticketnumber, Issue, Informers.UserName, Fixers.UserName, Supervisors.UserName FROM Tickets INNER JOIN Users Informers ON Tickets.EnteredBy = Users.UserID INNER JOIN Users Fixers ON Tickets.FixedBy = Users.UserID INNER JOIN Users Supervisors ON Ticket.ResponsibleUser = Users.UserID WHERE...
Sorry as i am not able to understand your words, but if I assumed your need correctly.. just for a try this could help you..
if you need either of them i.e. all users who has either entered or fixed or saw an issue you can find by..
Select t.Ticketnumber, t.EnteredBy, t.Issue, u.UserID ,u.UserName FROM Tickets t INNER JOIN Users u ON t.EnteredBy = u.UserID or t.FixedBy = u.UserID or t.ResponsibleUser = u.UserID;
And If you need all users who has entered, fixed and saw an issue you can find by..
Select t.Ticketnumber, t.EnteredBy, t.Issue, u.UserID ,u.UserName FROM Tickets t INNER JOIN Users u ON t.EnteredBy = u.UserID and t.FixedBy = u.UserID and t.ResponsibleUser = u.UserID;