StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I have had a deeper look at this and offer the following suggestion which includes changes to the data model also. This solution will also allow you to have the same book used for several modules (if required in the future). Also note the new table mod_books The full code for this (including the query) is below... hope its ok</p> <pre><code>CREATE TABLE IF NOT EXISTS books (book_id varchar(8) NOT NULL DEFAULT '', book_title varchar(100) DEFAULT NULL, author1 varchar(20) NOT NULL, author2 varchar(20) DEFAULT NULL, publisher varchar(20) NOT NULL, pub_year year(4) NOT NULL, PRIMARY KEY (book_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS courses (course_ID varchar(8) NOT NULL, course_title varchar(255) CHARACTER SET ascii NOT NULL, Entry_Year int(1) NOT NULL, Duration int(1) NOT NULL, PRIMARY KEY (course_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS modules (mod_id varchar(8) NOT NULL, mod_title varchar(255) NOT NULL, description varchar(255) NOT NULL, PRIMARY KEY (mod_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS mod_books (mod_id varchar(8) NOT NULL, book_id varchar(8) NOT NULL, PRIMARY KEY (mod_id,book_id), FOREIGN KEY (mod_id) REFERENCES modules (mod_id), FOREIGN KEY (book_id) REFERENCES books (book_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS mod_course (ID int(11) NOT NULL AUTO_INCREMENT, mod_id varchar(8) NOT NULL, course_ID varchar(8) NOT NULL, PRIMARY KEY (ID), FOREIGN KEY (mod_id) REFERENCES modules (mod_id), FOREIGN KEY (course_ID) REFERENCES courses (course_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ; </code></pre> <p>and the query would be</p> <pre><code>SELECT b.* FROM mod_course mc INNER JOIN mod_books mb ON mb.mod_id = mc.mod_id INNER JOIN books b ON b.book_id = mb.book_id WHERE mc.course_id = 'BIT' </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload