1

I have the two tables courses AND something_about_courses:

courses

  • course_code
  • course_session
  • course_type

something_about_courses

  • course_code
  • course_session
  • some_description

To ensure that I fetch some_description only for courses that actually exist, I use:

SELECT some_description FROM something_about_courses A JOIN courses B ON (A.course_code = B.course_code AND A.course_session = B.course_session)

Okay, everything is good and the system is put in to use. Now, two year after the system is in use, the client wants to compare not just with {course_code, course_session} but also with course_type.

So I change the structure of something_about_courses to:

  • course_code
  • course_session
  • course_type
  • some_description

and I change to query to:

SELECT some_description FROM something_about_courses A JOIN courses B ON (A.course_code = B.course_code AND A.course_session = B.course_session AND A.course_type = B.course_type) <<<<<<< AND clause added to JOIN

The problem I now face is that all old data in something_about_courses doesn't JOIN successfully since they have a NULL for course_type as it was a newly added column.

In other words, I have working queries but a disconnect in the data sets.


Solutions I have thought of:

  • Date based segmentation: use the old query for before the feature change and the new query for after. However, this isn't elegant and has a huge amount of maintenance overhead.
  • Manual adjustment of the old data: there is way too much of this to adjust it to be compatible with the new query to make this feasible.

Neither of the above solutions are ideal. I was wondering if there is anything I can do at the query level to JOIN on 2 or 3 columns based on whether something_about_courses.course_type IS NULL.

2 Answers 2

1

Try and see if this yields the result you want:

SELECT some_description
FROM something_about_courses A
JOIN courses B
ON (A.course_code = B.course_code
AND A.course_session = B.course_session
AND (A.course_type = B.course_type OR B.course_type IS NULL))
0

As a quick solution, use the <=> operator, so

AND (A.course_type <=> B.course_type).

As a real solution, add a column id to course and let something_about_courses show to that id column.

EDIT: If you want to get rid of the NULL, you don't need to do that manually. What about

UPDATE A set course_type=-1 where course_type IS NULL;
UPDATE B set course_type=-1 where course_type IS NULL;

as long as no real course type is "-1"?

But definitely: Use An Index Column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.