[EMAIL PROTECTED] wrote:
> John Salerno wrote:
> > [EMAIL PROTECTED] wrote:
> >
> > > [Eid] [Sid] [university] [yearStart] [yearEnd] [degreeEarned]
> > > 5 1 U of I 1971 1975 BS
> > > 6 1 U of I 1975 1976 MS
> > > 7 1 U of I 1976 1977 PhD
> > >
> > > where [Eid] is the primary key of the Education table and
> > > [Sid] is the foreign key from the Student table so that the
> > > single student record (1) links to three education records
> > > (5,6,7).
> > >
> >
> > Hmmm. I think I'm lost. My table will have a key that represents the
> > persons ID, in my case it would be salerjo01. This will be what ties
> > this table to that person. Can I repeat this key multiple times in the
> > Education table?
>
> Yes, because it would be a foreign key.
>
> Perhaps a simple example might help:
>
> import sqlite3
>
> con = sqlite3.connect(":memory:")
> cur = con.cursor()
>
> # in the [person] table, [personid] is the primary key
> # but in the [education] table, it's a foreign key
> # I deliberately didn't make them AUTOINCREMENT to
> # simplify the example
> cur.executescript("""
> create table person(
> personid PRIMARY KEY,
> firstname,
> lastname
> );
>
> create table education(
> educationid PRIMARY KEY,
> personid,
> institution,
> yearStart,
> yearEnd,
> degreeEarned
> );
> """)
>
> persons = [(1,'Tom','Smith'), \
> (2,'Dick','Smith'), \
> (3,'Harry','Smith')]
>
> degrees = [(1,1,'University of Illinois',1971,1975,'BS'), \
> (2,1,'University of Illinois',1975,1976,'MS'), \
> (3,1,'University of Illinois',1976,1977,'PhD'), \
> (4,2,'University of Illinois',1971,1974,None), \
> (5,2,'DeVry Institute of Technology',1974,1976,'ASEET')]
>
> cur.executemany("""
> INSERT INTO person(personid,
> firstname,
> lastname)
> VALUES (?,?,?)"""
> , persons)
>
> cur.executemany("""
> INSERT INTO education(educationid,
> personid,
> institution,
> yearStart,
> yearEnd,
> degreeEarned)
> VALUES (?,?,?,?,?,?)"""
> , degrees)
>
>
> # Note: since both tables have a field named [personid],
> # the table name must be included when referencing
> # that field
> cur.execute("""
> SELECT firstname,
> lastname,
> institution,
> yearStart,
> yearEnd,
> degreeEarned
> FROM education
> INNER JOIN person
> ON person.personid = education.personid
> ORDER BY education.personid;
> """)
>
> report = cur.fetchall()
>
>
> for i in report:
> print '%5s %-5s %-30s %d-%d %-6s' % (i)
>
> ## Tom Smith University of Illinois 1971-1975 BS
> ## Tom Smith University of Illinois 1975-1976 MS
> ## Tom Smith University of Illinois 1976-1977 PhD
> ## Dick Smith University of Illinois 1971-1974 None
> ## Dick Smith DeVry Institute of Technology 1974-1976 ASEET
>
> ## Note that the third person, Harry Smith, doesn't show up
> ## in the report.
> ## That's because no education records were created for him.
> ## When you do an INNER JOIN between two tables, the linking field,
> ## [personid] must exist in both tables.
If you actually DO want to see Harry Smith listed in the
report even though he has no education, you would change
the INNER JOIN to a RIGHT JOIN. But sqlite3 doesn't
support RIGHT JOIN, only LEFT JOIN. So we just have
to change the direction of the JOIN. Instead of from
education to person, make it it from person to
education and then we can use LEFT JOIN. And it might
be a good idea to sort on person.personid since
education.personid will be null if there is no matching
record.
The modified query becomes:
cur.execute("""
SELECT firstname,
lastname,
institution,
yearStart,
yearEnd,
degreeEarned
FROM person
LEFT JOIN education
ON person.personid = education.personid
ORDER BY person.personid;
""")
And I had to change the print statement since nulls
(which translate to None) crash the %d. With that
change we now can see ALL the people and note
that Harry is uneducated.
Tom Smith University of Illinois 1971-1975 BS
Tom Smith University of Illinois 1975-1976 MS
Tom Smith University of Illinois 1976-1977 PhD
Dick Smith University of Illinois 1971-1974 None
Dick Smith DeVry Institute of Technology 1974-1976 ASEET
Harry Smith None None-None None
--
http://mail.python.org/mailman/listinfo/python-list
