By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,610 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,610 IT Pros & Developers. It's quick & easy.

nested joins - joining one table to another multiple times

P: n/a
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

Jan 9 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ng
Hi Steve,

this sql-code will work:

select c.*, u1.id, u2.id, u3.id
from contact c
left outer join users u1 ON u1.username = c.creator
left outer join users u2 ON u2.username = c.modified
left outer join users u3 ON u3.username = c.owner
Steve C schrieb:
....

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."


www.zankl-it.de

Jan 9 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.