Database query of a single table using one lookup table?

September 8, 2017 8:40 AM

SQLFilter: I've been wracking my brains trying to come up with a query that will work to pull data out of a table where relationships between its records are defined in an external lookup table. More inside.

The database I'm working with belongs to a university, so the table in question is a list of courses with their internal IDs, names, and so on. So far, so simple.

However, the system has a concept of "child courses". What that basically means is that a course designated as a child course can inherit things that are attached to its parent, such as supplementary course materials (handouts, videos, etc.). The idea is that the instructor(s) can upload only one copy of the material to the parent course, and then anyone enrolled in its children will be able to see it. (That doesn't really affect my question--it's just context.)

Course information is stored in a COURSE_MAIN table, and the parent/child relationships are specified by the annoyingly-named COURSE_COURSE lookup table. It looks something like this:

COURSE_MAIN
PK1          COURSE_ID          COURSE_NAME
123          ENG101             ENGLISH 101
124          ENG102             ENGLISH 102

COURSE_COURSE
PK1          PARENT_COURSE_ID       CHILD_COURSE_ID
100          ENG101                 ENG102

...and the result set I'm looking for is something like this:

PARENT_COURSE_ID        PARENT_COURSE_NAME       CHILD_COURSE_ID        CHILD_COURSE_NAME
ENG101                  ENGLISH 101              ENG102                 ENGLISH 102

I've tried aliasing the COURSE_MAIN table and joining across the lookup table, but I keep getting "invalid identifier" errors out of Oracle. I'm pretty much to the point where I'd be tearing my hair out if I had any left, so I'm hoping one of you will be able to untangle this for me. Thanks in advance for any help and/or advice, and let me know if you need me to clarify anything--I'm writing this at a quarter to five on a Friday afternoon in the office, so I might not be as coherent as I think.

posted by Mr. Bad Example to Computers & Internet (15 answers total)

You basically just want to expand course_course with names?

I don't speak Oracle so there may be some dialect issues, but how about:

select c.parent_course_id, n1.course_name as parent_course_name, c.child_course_id, n2.course_name as child_course_name
from course_course as c
left join course_main as n1 on c.parent_course_id = n1.course_id
left join course_main as n2 on c.child_course_id = n2. course_id
posted by allegedly at 8:58 AM on September 8, 2017

Is the issue that you want the child_course_name, but that's only in the course_main table? Or is there some other issue? If that's it, try this:

SELECT
  course_main.course_id as parent_course_id,
  course_main.course_name as parent_course_name,
  course_child.child_course_id,
  course_child.child_course_name
FROM
  course_main
  LEFT JOIN  
      (SELECT
         course_course.*,
         course_main.course_name as child_course_name
      FROM
         course_course
         LEFT JOIN course_main on course_course.child_course_id = course_main.course_id) course_child
    ON course_main.course_id = course_child.parent_course_id

(I disavow any typos in that code. Also the word "course" has lost all possible meaning in my brain)
posted by brainmouse at 8:59 AM on September 8, 2017

Option 2 that doesn't do a subselect:

SELECT
  course_main.course_id as parent_course_id,
  course_main.course_name as parent_course_name,
  course_course.child_course_id,
  course_main2.course_name as child_course_name
FROM
  course_main
  LEFT JOIN course_course ON course_main.course_id = course_course.parent_course_id
  LEFT JOIN course_main course_main2 ON course_course.child_course_id = course_main2.course_id

I think either of those options should work
posted by brainmouse at 9:02 AM on September 8, 2017 [1 favorite]

Yep, brainmouse's option 2 is probably best. Here's a sqlfiddle that demonstrates the same kind of join.
posted by specialagentwebb at 9:04 AM on September 8, 2017

A slight variant, but it gives exactly the same results as brainmouse's query:

select course_id, 
       course_name as parent_name, 
       child_course_id, 
       (select course_name from course_main where course_id=child_course_id) as child_name 
       from course_main 
    LEFT OUTER JOIN COURSE_COURSE
       ON parent_course_id = course_main.course_id

posted by samj at 9:13 AM on September 8, 2017

And another variant which doesn't use ANSI join syntax, but does it the old-style Oracle way instead, with join conditions in the where clause:

select parent.course_id
, parent.course_name
, child.course_id
, child.course_name
from course_course
, course_main parent
, course_main child
where course_course.parent_course_id = parent.course_id
and course_course.child_course_id = child.course_id
and parent.course_id = 'ENG101'

All of these are the same query, they just have aesthetic differences.

The data model would be easier to understand if there was a parent_course_id column on the course_main table, which is null if it doesn't have a parent. Then you'd need to self-join only once.
posted by rd45 at 9:59 AM on September 8, 2017 [1 favorite]

Link to solution
posted by busybee at 10:18 AM on September 8, 2017

Clarifying questions. Are all courses in the course_course table? What if there's a course with no children or parent? Should they be in the report with one side blank? In which column should that one be in, parent or child?
posted by advicepig at 12:08 PM on September 8, 2017

The data model would be easier to understand if there was a parent_course_id column on the course_main table, which is null if it doesn't have a parent. Then you'd need to self-join only once.

this. totally. more than 'makes it easier', it repairs 3nf. can you refactor the tables? or at least put it in the backlog?

this is an example of a 'self referencing table'
posted by j_curiouser at 1:00 PM on September 8, 2017

Response by poster: Are all courses in the course_course table? What if there's a course with no children or parent?

The only records in course_course are for courses that have a parent/child relationship (as far as I can tell, anyway), so if a course doesn't have a parent or child, it won't be in the table.

can you refactor the tables?

Imagine an emoji for "bitter laugh followed by a drawn-out NOOOOOPE". This is a commercial off-the-shelf product with a not-very-well-documented schema, and I'm not entirely sure what uses these tables. On top of that, I don't think we have full permissions to the database--it's hosted on the company's servers in Amsterdam, for one thing--so I'm reasonably sure I couldn't alter table structures without a huge amount of effort and/or expense.

Thanks, everybody. I'll give it a shot when I'm back in the office on Monday and report back.
posted by Mr. Bad Example at 1:17 PM on September 8, 2017

What should happen if a course is not in a parent child relationship?
posted by advicepig at 2:18 PM on September 8, 2017

Best answer: select cm.course_id, cm.course_name, cm2.course_id, cm2.course_name
from course_main cm, course_main cm2, course_course cc
where cm.pk1=cc.crsmain_parent_pk1
and cm2.pk1=cc.crsmain_pk1;

The primary communication forum for Blackboard technical discussions is BBAdmin-L. The schema is available online, and you may find this presentation useful.
posted by idb at 9:09 AM on September 10, 2017

Response by poster: What should happen if a course is not in a parent child relationship?

Then it just lives over in the course_main table, and doesn't have an entry in course_course--as far as I can tell, that table's just for storing parent/child relationships.

Thanks, everybody. I've got a report now that looks like what I needed, and I'll be running it past the people who need it later to see if they think it looks okay. You've saved me pulling out what little hair I had left. :)
posted by Mr. Bad Example at 1:48 AM on September 11, 2017

Sorry, my clarifying question wasn't clear. If there isn't a parent child relationship, should the course appear in your report?
posted by advicepig at 6:35 AM on September 14, 2017

Response by poster: Oh, I see. The short answer is no--I only wanted courses with relationships in that particular report.

The longer, more complex answer is that all of this wound up forming a subquery of a larger and much uglier query, and its sole purpose in life is now to pass butter pull the parent course ID into the main report if one exists. (Otherwise that column stays blank.) It was one of those "It would be nice to have" requests that wound up being a lot more trouble than the requester realized.
posted by Mr. Bad Example at 4:59 AM on September 19, 2017