List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 7 2000 2:06pm
Subject:Re: A Tale of Three Tables
View as plain text  
At 10:00 AM -0400 04-07-2000, Margie Wiers wrote:
>Maybe I didn't populate the master table correctly.  Right now the results
>show the following.  Maybe a better question is:  what should this table
>represent?

That's a question you'll have to answer! :-)

But it looks indeed to me that the table isn't populated correctly.
I would expect rows something along the following lines: Suppose
Joe has member_id of 13 and went to College X that has college_id of 48.
Then your master table should have a row in it with columns
college_id = 48, member_id = 13.

>
>mysql> select * from master;
>+----------+-----------+
>| college_id | member_id |
>+----------+-----------+
>|        1 |         1 |
>|        2 |         2 |
>|        3 |         3 |
>|        4 |         4 |
>|        5 |         5 |
>|        6 |         6 |
>|        7 |         7 |
>|        8 |         8 |
>. . .
>
>8 rows in set (0.33 sec)
>
>Thanks,
>Margie
>
>>  >
>>  >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