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?

Table1: Tickets

1) Ticketnumber
2) EnteredBy             User 100
3) Issue
4) FixedBy               User 102
5) FixedByNotes
6) ResponsilbeUser       User 103

Table2: Users

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

Thanks Steve

Answers


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;

Need Your Help

Looping input files with the file class

java loops input output

I am trying to figure out how to loop a filename. I have 10 files