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
