I'm working on our school's website and need help with the courses section. We have two tables:
1. tbl_course_details: Stores course info (id, name, type, etc.)
2. tbl_course_related: Links related courses
When staff edit a course, they see other courses with checkboxes to mark related ones. For example:
Courses:
1. Geography
2. History
3. Art
Relationships:
- Geography related to History
- History related to Art
Problem: When editing Geography, I want to show History (checked) and Art (unchecked). My current query shows duplicates:
History (No)
History (Yes)
Art (No)
How can I fix this to show each course once with the correct relationship status? Here's my current SQL:
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
Any suggestions to improve this query?
Hey there, ClimbingMountain! 
That’s a tricky situation you’ve got there with your course relationships. I can totally see why you’re getting those pesky duplicates. Have you considered using a subquery instead? Something like this might do the trick:
SELECT d.uid, d.course_name,
CASE WHEN d.uid IN (
SELECT related_course_id
FROM tbl_course_related
WHERE course_id = :courseId
) THEN ‘Yes’ ELSE ‘No’ END AS is_related
FROM tbl_course_details d
WHERE d.uid != :courseId
This way, you’re checking if each course is related in a subquery, which should avoid the duplicate issue. Plus, it might be a bit easier to read and maintain in the long run.
What do you think? Have you tried anything like this before? I’m really curious to hear if this helps or if you’ve found another solution. School websites can be such a pain sometimes, right? 
Oh, and just out of curiosity - how many courses are you dealing with in total? I wonder if performance might become an issue with a really large dataset. Any thoughts on that?
I’ve dealt with a similar issue in a project for a local community college. Your approach is on the right track, but you need to tweak the JOIN condition slightly. Try this query:
SELECT d.uid, d.course_name,
CASE WHEN r.related_course_id IS NOT NULL THEN ‘Yes’ ELSE ‘No’ END AS is_related
FROM tbl_course_details d
LEFT JOIN tbl_course_related r ON r.related_course_id = d.uid AND r.course_id = :courseId
WHERE d.uid != :courseId
This should give you a single row per course, with ‘Yes’ or ‘No’ indicating if it’s related. The key is joining on related_course_id instead of course_id, and adding the condition for the current course in the JOIN clause.
Remember to replace :courseId with your actual parameter. This worked well in my case, hope it helps!