|
Posted by Steve C on 01/08/06 14:04
Hi,
I'm having problems constructing a nested join. It's quite complex, so
here's a simplfied example of the problem. Any thoughts on what I'm
doig wrong - or if I've got the whole approach wrong are welcome.
I've two tables :-
one is a contact table contacting name, addresses etc. Three of the
fields represent users - 'created by', 'last modified by' and 'owner'.
They contain usernames - eg. JDOE, BSMITH etc.
The other table contants usernames and new ID codes.
What I want to do is create a new dataset by joining the contacts table
with the user table on all three fields - so the new dataset contains
the ids for the creator, last modifier and owner.
I've tried things similar to:
select c.*, u1.id, u2,id, u3.id
from contact c
left outer join users u1
left outer join users u2
left outer join users u3
on (u3.username = c.owner)
on (u2.username = c.modified)
on (u1.username = c.creator )
But it compains that
"The column prefix 'c' does not match with a table name or alias name
used in the query."
The problem is referencing c (contact) through the whole set of joins.
I would like to do this in some similar format as the query is within a
cursor and post-processing would be very long-winded.
Thanks
Navigation:
[Reply to this message]
|