Two Fields Linked to Another Table 1

[NMiller007]

I have a User tables that contains user information with the login name as the primary key. This is the only place I am storing the full name of users.

I have a table that contains requests. The table stores the login name for the person who made the request and also the login name for the person who approves the request.

In my query I need both names, so they can be printed on a report.

How can this be accomplished?

 

[AlexCuse]

I am not too clear on what you're trying to do here, but it sounds like you want something like this (not tested):

select User.Name, Approver.Name
from User inner join Request on User.LoginName = Request.RequestorLoginName
inner join (select User.Name, Request.RequestorLoginName  from User inner join Request on User.Name = Request.ApproverLoginName) Approver on Request.RequestorLoginName = Approver.RequestorLoginName

You will need to mess with your table and field names of course.

If this does not get you going in the right direction, please post back with sample data and desired result.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[NMiller007]

I'm getting syntax errors when I try to enter that code with my table names, so maybe I should just give some better information.

I have a table called Users, which has user_id and user_name.

I have a table called Requests which has RequestedBy and ApprovedBy (which contain the respective Requestor and Approver's user_id).

I want to create a query that displays

ApprovedBy RequestedBy ....
Jim Smith Jack Johnson ....

I have jsmith and jjohnson in the table, but I need to pull both full names from the Users table.

Is that a little better?

Thanks.

 

[AlexCuse]

was teh SQL you tried this?:

select Users.user_name as Requestor, Approver.user_name as Approver
from Users inner join Requests on Users.user_id= Requests.RequestedBy 
inner join (select Users.user_name, Requests.ApprovedBy  from Users inner join Requests on Users.user_id = Requests.ApprovedBy) Approver on Requests.ApprovedBy = Approver.ApprovedBy

I think I made all necessary changes to your table and field names, let me know if this works. If not, we'll figure something out.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[NMiller007]

I got it working by using a second query. Here's the query:

SELECT Users.user_name AS Requestor, ApprovedByName.user_name AS Approver
FROM (Requests INNER JOIN ApprovedByName ON Requests.ApprovedBy = ApprovedByName.ApprovedBy) INNER JOIN Users ON Requests.RequestedBy = Users.user_id;

Thanks for the help!

 

[PHV]

No extra query required !
SELECT R.user_name AS Requestor, A.user_name AS Approver
FROM (Requests
INNER JOIN Users AS R ON Requests.RequestedBy = R.user_id)
INNER JOIN Users AS A ON Requests.ApprovedBy = A.user_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[NMiller007]

Yes, that one worked. I don't know what I was doing wrong the first time through.

Thanks!