Discussion Locked

multi-table with alias

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I've got a database with numerous tables. I want to do a query that is like:
Select projnum, projtitle, projmgr, projclerk from
Projects inner join people where people.peoplecnt=projects.projmgr

I get that part to work. Now the projmgr and all other people assoc with the project are in the people table and are identified by the peoplecnt field (which is the data in the project table for the person by function - ie, projmgr = peoplecnt and projclerk = peoplecnt, etc.). The people are stored as peoplecnt, fname, lname, userid, (etc.) in the people table.

Now what I'm trying to do is:
Select projnum, projtitle, fname + " " + lname as "Project Manager", fname + " " + lname as "Project Clerk" from project inner join people on project.projmgr = people.peoplecnt

When I do this all the people are the proj mgr - how do I do multiple on's?

Thanks!!
Jim

3 replies

by tony hopkinson

Select ... From Table1, Table2 Alias1, Table2 Alias2
Where table1.link1 = alias1.link1
and table1.link2 = alias2.link2
Should work.

P.S. Technical Q & A.
I'm missing out on my points.

by jamesski

Oops, I guess I need to clarify. It's the alias of the Select that's giving me some grief. The Select fname + " " + lname "Project Manager", is the same as the ... fname + " " + lname "Project Clerk" since they both come from the people table and peoplecnt...when I run it they all fill in with the Project Manager info (since it's the first the query encounters?). All the people come from the same table with a peoplecnt and they all can be one thing on one project and another on a different project. So the answer needs to be in the Where part tied to the Select part... somehow - haven't seen an example anywhere of this...

by deepsand

This is a tech. problem, in search of a solution.

It properly belongs in "Technical Q&A," not in "Discussions." Please re-post there.

Not only will you be more likely to attract the attention of those seeking to provide answers, but you will also make it easier for those seeking discussions to find such.

Thank you.

This discussion is closed to new replies.