select/join problem

[davidste]

I am currently building a projects asp application and designing the database for this. At the moment I am having problems.

I have 2 tables t_proj (projects info) and t_users (user info). Projects contains projects info and users contains all the users of the app, information on the user (email etc) and their different access levels.
ie certain users should be able to add projects etc but some should not.

My problem is that every project has a manager and a sponsor associated.

The t_proj table has the following fields
f_proj_id (pk)
f_name
f_manager_id (related to t_users.f_user_id)
f_sponsor_id (related to t_users.f_user_id)
etc.....

The manager and sponsor id relate to the t_users table which has the following fields.
f_user_id(pk)
f_forename
f_surname
etc.....

I want to select the info in t_proj but return f_manager_id and f_sponsor_id as the forname and surname of the appropriate user.

If I try a usual Select I get

SELECT t_proj.f_proj_id, t_proj.f_name, t_users.f_forename, t_users.f_surname FROM t_proj INNER JOIN t_users ON t_proj.f_manager_id = t_users.f_user_id

But then can not do another join to get the sponsor without doing another select.

I have tried creating a table for managers t_mgr and a table for sponsors t_sponsor both with
f_user_id and f_proj_id but still get the same problem.

I know I can return all the ids from the t_proj table then maybe do another select based on what is returned, but this seems quite cumbersome.

Is there a table I am missing, am I just being dumb?
I am fairly new to this.

 

[jbenson001]

So what is the problem? You code looks like it should work for what you describe.

 

[davidste]

The returning everything from t_proj then having to do another query based on the result set seems cumbersome thought that there may be a way to select the project info and return the managers forename/surname and sponsors forename/surname in one go that I may be missing.

Seems like it should be such a simple thing to do.

Also I am having to start developing this in access and upgrading to SQL later, and have not too much experience of SQL What method would you say is best? and how would be best to do this in SQL?

 

[jbenson001]

Sorry not understading your problem. Please show wht you are getting and what you WANT to get back

 

[davidste]

In one SELECT statement I want to return
f_proj_id, f_name, f_manager_id (converted to forename and surname from t_users), f_sponsor_id(converted to forename and surname from t_users)

At the moment I can not join to the users table from the project table and pull back the forename and surname for the manager id and sponsor id. Maybe I can and my logic is wrong I am not sure.

Hope this helps sorry if I am not explaining myself very well.

 

[jbenson001]

Why cant you join.. you have the IDs in both tables? You code you wrote should work..

What I was asking was to post the output you WANT versus the output you are GETTING..


Jim

 

[davidste]

The problem is my SELECT statement would need to do two INNER JOINS but to the same table and to the same fields in that table.

What I want to do is

SELECT t_proj.f_proj_id, t_proj.f_name, t_users.f_forename (for manager),
t_users.f_surname (for manager), t_users.f_forename (for sponsor), t_users.f_surname(for sponsor),  FROM t_proj INNER JOIN t_users ON t_proj.f_manager_id = t_users.f_user_id INNER JOIN t_users ON t_proj.f_sponsor_id = t_users.f_user_id

The error I get is
Syntax error (missing operator) in query expression 't_proj.f_manager_id = t_users.f_user_id INNER JOIN t_users ON t_proj.f_sponsor_id = t_users.f_user_id'.

 

[johpje]

davidste,

you should use table aliases for each diferent instance of t_users:

SELECT t_proj.f_proj_id, t_proj.f_name, t_manager.f_forename,
t_manager.f_surname, t_sponsor.f_forename, t_sponsor.f_surname
 FROM t_proj INNER JOIN t_users t_manager ON t_proj.f_manager_id = t_manager.f_user_id INNER JOIN t_users t_sponsor ON t_proj.f_sponsor_id = t_sponsor.f_user_id

regards,
Johan

 

[davidste]

Thank you very much for your help just tried

SELECT t_proj.f_proj_id, t_proj.f_name, t_user_one.f_forename, t_user_one.f_surname, t_user_two.f_forename, t_user_two.f_surname  FROM t_proj INNER JOIN t_users AS t_user_one ON t_proj.f_proj_mgr_id = t_user_one.f_user_id INNER JOIN t_users AS t_user_two ON t_proj.f_proj_sponsor_id = t_user_two.f_user_id

and I got the following error
Syntax error (missing operator) in query expression 't_proj.f_proj_mgr_id = t_user_one.f_user_id INNER JOIN t_users AS t_user_two ON t_proj.f_proj_sponsor_id = t_user_two.f_user_id'.

Think it may have more to do with the way I have listed the Inner Joins though. Thank you again for your help it is greatly appreciated.