467,926 Members | 1,492 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,926 developers. It's quick & easy.

nested joins - joining one table to another multiple times

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
  • viewed: 6194
Share:
1 Reply
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.

Similar topics

3 posts views Thread by Prem | last post: by
3 posts views Thread by jakelake | last post: by
reply views Thread by hotgazpacho | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.