Thread: Help with query

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

Ryan,

> 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;

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

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

On Wed, 11 Aug 2004 21:48:50 -0700, Josh Berkus <josh@agliodbs.com> wrote:
> Ryan,
>
> > 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;
>
> 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
>