Setting up MySQL cross-listing query

I have a table Classes, which stores data on different college classes (a unique Class_ID and Department, Course, etc.).

Some Classes are cross-listed with multiple names. For example, the same class might be cross listed as HIST-204 (History) and AAST-103 (African American Studies). There can be more than 2 cross-listings for a class.

I'd like to store cross-listings properly, so that when students search for a class's textbooks (stored in Class_Books table) using any of its possible cross-listings, they are shown the required books. It is not important whether students know about other cross-listings when they make a search, only 开发者_运维百科that they get the books for that course.

I'm thinking of doing this by storing a Cross_List_ID in Classes, which would indicate that a given class is just a cross listing of an existing one, whose Class_ID would be stored in that field. Then I would only need to store the required book (in Class_Books) for the Class_ID that was cross listed. Then when students enter a class in the search, I'd make sure to include all books required for the Cross_List_ID. If you agree that this is the way to go, I'd be interested in how you'd set up that query (to get the class-books for the class_id entered, as well as its cross-listing).

One other option would seem to be storing Class_Books data for every cross listing.. I don't want to do this method because the raw Class and Class-Book data which I upload to the database doesn't lend itself to this, because there are separate files for each, and only one of the cross-listings is linked to a Book in the Class-Book file. Also, even if this method were easily feasible, it would use up more database space.

I'm open to other options if you disagree with my Cross_Listing_ID approach. Like I said, if you agree with that approach then help me make a query!

Note: Structure of Class-Books is just Class_ID, Book_ID

If you have to pick between the two go with the first option. You don't want to have ostensibly duplicate entries in class books. That can cause all sorts of problems for you.

Update Since you want Class ID to be in the where clause I've updated it.

As for the query this is how I would search for books

SELECT DISTINCT books.*
FROM
    classes main
    LEFT JOIN classes crosslinked
    ON main.Class_id = crosslinked.cross_link_class_id
    LEFT JOIN class_books books
    ON main.class_id =  books.class_id
WHERE
    main.class_id= 252
    OR
    crossLinked.class_id= 252 

But you should take ijw's point. It would be much better to just have a class_names table that's associated with the class. It would be a bit cleaner. Then you don't have to do logic like : if cross_link_class_id is not null then don't allow students to register for it, a professor can't teach a class that has a null for cross_link_class_id.., after all you wouldn't want to accidentally overbook a class or double the number of classes a professor was being recorded as teaching

Here's how it would be done with a separate class names table.

SELECT DISTINCT books.*
FROM
    classes 
    --LEFT JOIN class_names names
    --ON main.Class_id = names.Class_id 
    LEFT JOIN class_books books
    ON main.class_id =  books.class_id
WHERE
   clases.class_id  = 252 

Notice the OR statement is gone and no funny self join (which can confuse people) and the aforementioned logic on empty cross_link_class_id is gone.

Note: I've taken some liberties with the query e.g the literal string would be replaced with a parameter. You wouldn't select * but use the actual field names.