List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 7 2000 1:34pm
Subject:Re: A Tale of Three Tables
View as plain text  
At 9:13 AM -0400 04-07-2000, Margie Wiers wrote:
>Hello all--
>
>Happy Friday!
>
>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 table
>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)
>college_id, collegename
>
>Master table (pairs member with college)
>member_id, college_id
>
>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
>here?

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 
=college.college_id;

-- 
Paul DuBois, paul@stripped
Thread
A Tale of Three TablesMargie Wiers7 Apr
  • Re: A Tale of Three TablesPaul DuBois7 Apr
  • Re: A Tale of Three TablesMargie Wiers7 Apr
    • Re: A Tale of Three TablesPaul DuBois7 Apr
  • Re: A Tale of Three TablesMargie Wiers7 Apr
    • Re: A Tale of Three TablesPaul DuBois7 Apr
  • Re: A Tale of Three TablesMargie Wiers7 Apr
  • Re: A Tale of Three TablesMargie Wiers7 Apr