MySQL Create list of related courses

Question

I'm updating a website for our school, and we have a list of courses available.

The list of courses is in tbl_course_details:

CREATE TABLE `tbl_course_details` (
    `uid` int(11) NOT NULL auto_increment,
    `course_name` varchar(100) NOT NULL,
    `course_type` varchar(100) NOT NULL,
    `course_details` longtext NOT NULL,
    `course_added_by` int(4) NOT NULL,
    `course_last_updated` int(30) NOT NULL,
    `course_menu_id` int(4) NOT NULL,
    PRIMARY KEY  (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15;

I also have a table called tbl_course_related:

CREATE TABLE IF NOT EXISTS `tbl_course_related` (
    `uid` int(11) NOT NULL auto_increment,
    `course_id` int(4) NOT NULL,
    `related_course_id` int(4) NOT NULL,
    PRIMARY KEY  (`uid`),
    KEY `course_id` (`course_id`),
    KEY `related_course_id` (`related_course_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=25;

When a staff member edits a course, they also get a list of all courses (Except the course they are currently editing). It also has check boxes next to them, so that if a course is related to the course they are editing, they tick the box.

My course details table has 3 courses in it at the moment:

Unique ID    Course Name
---------------------------
1            Geography
2            History
3            Art

As an example, i've made geography related to history, and history related to art, so my relationship table looks like:

Unique ID    Course ID     Related Course Id
--------------------------------------------
1            1             2
2            2             1
3            2             3
4            3             2

I hope this makes sense so far. Now, If a staff member went to edit the Geography Course (course ID: 1), then I would like a list of courses to contain History and Art, and only the history checkbox is ticked.

My current SQL query looks like this (although I have changed it around a bit and nothings worked so far):

SELECT d.uid, 
       d.course_name,
       r.related_course_id
FROM tbl_course_details d
LEFT JOIN tbl_course_related r
    ON r.course_id = d.uid 
AND d.uid!=:courseId

However this makes the list look like this (if I edit geography)

Related?   Course Name
No         History
Yes        History
No         Art

Obviously I dont want history in the list twice, especially with conflicting information.

Any ideas?

Answer 1

Here is a SQLFiddle demo. If in result dataset r.related_course_id is not NULL then you should tick a checkbox

SELECT d.uid, 
       d.course_name,
       r.related_course_id
FROM tbl_course_details d
LEFT JOIN tbl_course_related r
    ON (r.course_id = d.uid) and (r.Related_Course_Id=:courseId)

where d.uid!=:courseId