Hi Ash,
> I have tried all sorts of joins and statements without much success... I can
> obtain one name or both names if they are the same... but not different
> names together in the one record.
=how about some example code showing what you are doing?
At the very least it gives me a 'starting point' and saves my time/typing.
Also without the 'hints' communicated by your code, I might assume you are 'smarter',
or more of a beginner,
than you really are!
=it sounds as if it is not the 'type' of join that is the 'problem', but the number of
joins.
A 'stock standard' LEFT INNER JOIN is quite sufficient.
The 'trick', as I mentioned, is to set up two joins!
> Can you possibly provide an example of the specific joins you are talking
> about. I think I must be missing something fundamental here.
Let's start with the project table and simply list that:
SELECT ProjectId, ProjectOwner, ProjectManager
FROM AshProject
ProjectId ProjectOwner ProjectManager
A12345 1 2
Now let's bring in the User tbl and 'translate' the ProjectOwner's Id/nr into his/her
name. Put in one join and
output the name of the ProjectOwner (as well as his/her number, for debugging
purposes):
SELECT ProjectId, ProjectOwner, ProjectManager, FullName AS ProjectOwner
FROM AshProject, AshUser
WHERE ProjectOwner = UserId
ProjectId ProjectOwner ProjectManager ProjectOwner
A12345 1 2 Bob Smith
So far, so good. Now we need to translate/add the ProjectManager's name!
Persisting with a single join and trying to add WHERE ... AND ProjectManager = UserId,
results in it all turning
to ashes, because MySQL goes looking for a SINGLE UserId value that will equal *both*
names, and such will only
happen if the Project's Manager and Owner are the same person! (in the case of the
limited sample data provided,
never!)
We need to add a second version/copy of the User tbl to the query, and distinguish
between the two 'versions' by
using aliases - that way you can apparently use UserId 'twice', ie two *separate*
UserIds and thus the two names
can be retrieved from different rows of the User tbl (or twice from the same row, if
required):
SELECT ProjectId, ProjectOwner, ProjectManager,
AU1.FullName AS ProjectOwner, AU2.FullName AS ProjectManager
FROM AshProject, AshUser AS AU1, AshUser AS AU2
WHERE ProjectOwner = AU1.UserId
AND ProjectManager = AU2.UserId
ProjectId ProjectOwner ProjectManager ProjectOwner ProjectManager
A12345 1 2 Bob Smith John Smith
Once you've checked the logic/implementation, don't forget to remove the 'workings'
and then you'll have the
answer required.
Ok now?
=dn
> > > I am using MySQL 3.22.32 and are trying to accomplish the following
> (without
> > > going into too much detail, this is an example of the exact
> situation)...
> > >
> > > 1) I have two tables:
> > >
> > > a) User table containing: UserID, FullName
> > > b) Project table containing: ProjectID, ProjectManagerID and
> ProjectOwnerID
> > >
> > > ProjectManagerID and ProjectOwnerID are effectively UserIDs from the
> User
> > > table.
> > >
> > > 2) When I pull a particular record from the database by ProjectID, for
> > > readability purposes, I would like the accompanying ProjectManagerID and
> > > ProjectOwnerID to
> > > be displayed as a name, not an ID (for example: John Smith, not A12930).
> > >
> > > Has anyone got any ideas how I can select (within one record) both names
> > > from the User table by each respective UserID (represented by the
> > > ProjectManagerID and the ProjectOwnerID)??
> >
> >
> > This is quite logical (when you look back at it!). Set up two joins from
> Project to User, the first equating
> > ProjectManagerID to UserID and the second ProjectOwnerID to UserID - just
> because User is only one table,
> > doesn't mean you can't have multiple ways of joining to it!
> >
> > If that's not it, please send the query you have so far.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
