I'm new to SQL and I am having a little difficulty trying to construct
a query that will use the first table in the following list:

TABLE coaching_relationships:
coaching_relationship_id
coach_id
athlete_id

TABLE athletes
athlete_id
user_id

TABLE coaches
coach_id
user_id

TABLE users
user_id
user_name

And use sub-queries (or joins???) to return user names for the coaches
and athletes like this:

Coach | Athletes
=================
Bill | Marge
Bill | Anne
Mary | Judith
Simon | Esther
Simon | Raymond
Simon | Phyllis

....etc...

I can't seem to make the leap from this:

select c.user_id, a.user_id from athletes a, coaches c,
coaching_relationships cr where a.athlete_id = cr.athlete_id and
c.coach_id = cr.coach_id;

which gives me:

user_id | user_id
---------+---------
1 | 1
8 | 1
9 | 2
3 | 4
3 | 5
3 | 6
8 | 10
8 | 11
9 | 7

To resolving it to names...can I use a sub-query to do this? Any
chance somebody can show me how?

Thanks,

Ryan

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Ryan,

You're close:

select cuser.username as coach, auser.username as athlete
from athletes, coaches, coaching_relationships co_rel,
users cuser, users auser
where athletes.athlete_id = co_rel.athlete_id
and coaches.coach_id = co_rel.coach_id
and coaches.user_id = cuser.user_id
and athletes.user_id = auser.user_id
order by cuser.username, auser.username

And some unsolicited advice: don't abbreviate table names which are less than
10 characters. When you have to revisit these queries in 9 months, you won't
want to see all those "c" and "a" and "a1" tablename aliases. It's like
using programming variables named "x" and "y".

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Many thanks Josh for the solution and the advice...

On Wed, 11 Aug 2004 21:48:50 -0700, Josh Berkus <josh (AT) agliodbs (DOT) com> wrote:
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings