At 9:13 AM -0400 04-07-2000, Margie Wiers wrote:
>I've been studying the Manual, archives, and Paul's book for a simple design
>of three tables and the corresponding code. I almost have it. Maybe
>someone can point out this missing or erroneous part.
>member_id, lastname, firstname, etc.
>College table (first college/university at which a member studied; member
>is required to select a choice (none is also a choice); this is a long list,
>but only two columns)
>Master table (pairs member with college)
>It's this third table of which I am unsure. If each column contains a list
>of IDs, they will not correspond. Member #1 probably didn't to to college
>#1. Member #100 probably didn't go to college #100. What am I missing
They won't correspond to what? I assume that each row in the table will
list a member and the college the member *did* attend (i.e., that they *will*
correspond). I don't get what you mean here.
>Here is the code I constucted to find which colleges all the members
>attended (I found this code in the archives. Two LEFT JOINs possible?)
>SELECT lastname, firstname, collegename
>FROM member LEFT JOIN master
>ON member.member_id = master.member_id LEFT JOIN college
>ON college.college_id = master.college_id;
I doubt if you want LEFT JOIN here, because that will show you rows even
when there are no matches between tables. Does this do what you want?
SELECT lastname, firstname, collegename
FROM master, member, college
WHERE master.member_id = member.member_id AND master.college_id
Paul DuBois, paul@stripped