Scenario: Databases to list names, titles, contact information, and other
professional stuff about faculty members, administrators, and staff in a
university department, used (among other things) as a backend to a series of
Web pages with listings.
I had had three tables: faculty, administration, and staff, each with
columns relevant to each category. But I was always bothered by the fact
that administrators are also faculty members, so I was duplicating
information in the databases, which is _bad_! So now the the administration
table has, hard-coded (so to speak), the titles, administrative office
numbers, phone numbers, and other stuff that remains the same regardless of
who holds the position, and then an id column cross-referencing to the
faculty table for that individual member. So now all columns are full with
the (relatively) invariant data, and the last column, id, is empty (NULL).
BTW, the first column, "ordr," shows the order in which the administrators
should be listed, and is a primary key:
CREATE TABLE administrators (
ordr TINYINT UNSIGNED NOT NULL PRIMARY KEY,
title VARCHAR(64) NOT NULL,
building ENUM("Main Building","Annex") NOT NULL DEFAULT 1,
room TINYINT(3) UNSIGNED ZEROFILL NOT NULL,
telephone VARCHAR(16) NOT NULL,
id INT UNSIGNED NOT NULL,
INDEX(id)
);
Here is what I would want to do:
INSERT INTO administrators (id) values
(SELECT id FROM faculty
WHERE lastName = "Jones")
WHERE ordr = 1;
How can I do the equivalent of this in MySQL 3.23? What about MySQL 4.0?
Thanks!
Amittai Aviram
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
