Replace course_id with course_name in results

Question

I have schedules of class times and I place the course id in the classtimes. When I run the query I see that in 30 minute times what courses are scheduled, but in the results I want to see the name not the id.

SELECT        TOP (2000) classtimes.classtime_id, classtimes.classroom_id, classtimes.location_id, classtimes.classtime_day, classtimes.timec_9, classtimes.timec_95, classtimes.timec_10, classtimes.timec_105, 
                     classtimes.timec_11, classtimes.timec_115, classtimes.timec_12, classtimes.timec_125, classtimes.timec_13, classtimes.timec_135, classtimes.timec_14, classtimes.timec_145, classtimes.timec_15, 
                     classtimes.timec_155, classtimes.timec_16, classtimes.timec_165, courses.course_name FROM            classtimes INNER JOIN
                     courses ON classtimes.timec_10 = courses.course_id

If it finds a match I would like it to display the course_name instead of the course_ID

I will add a screen shot of the results

In this query we are looking for classes in the 10 am time frame and it finds 5 matchs and displays them. I can see the course names off to the side but I would like to see them in the timec_10 field.

Please do not paste images of code or data, please paste in as text only — Charlieface, May 28 '23 at 11:41
The design of your classtimes table is wrong, it should have been just classroom_id, location_id, day, time columns, then you only need one join and a pivot — Charlieface, May 28 '23 at 11:43
How is it wrong? There are different classes in each room in 1 hour or half hour segments? For each day of the week. I am storing the course id that are happening during those hours in this table. So it is storing the classroom the day of the week and what is schedule for the day for that room. — Rinkratt, May 29 '23 at 03:54
Because storing similar data in duplicate columns is a denormalization. You are supposed to normalize it into separate rows. As you can see, denormalized data is much harder to work with, causing you to have 16 separate joins when you only needed one — Charlieface, May 29 '23 at 04:49
But I would have to make those queries independent of each other because they wouldn't be in order. So right now I only need 1 query to get all the data, it would take 688 queries to do it in a way you're suggesting. — Rinkratt, May 29 '23 at 16:21
No, you just do one big join FROM classtimes ct JOIN courses c ON c.course_id = ct.course_id then you pivot it up by classroom_id and day (in each row) and show a value for each individual time in a separate column. All one query something like this https://dbfiddle.uk/rswpaSNN — Charlieface, May 29 '23 at 19:57

score 0 · Answer 1 · answered May 28 '23 at 09:03

I used the following to fix the issue but hoping there is a better/easier way to do this

SELECT        TOP (2000) classtimes.classtime_id, classrooms.classroom_rname AS Room, classtimes.classtime_day AS DOW, courses.course_name AS '9:00', courses_1.course_name AS '9:30', 
                         courses_2.course_name AS '10:00', courses_3.course_name AS '10:30', courses_4.course_name AS '11:00', courses_5.course_name AS '11:30', courses_6.course_name AS '12:00', 
                         courses_7.course_name AS '12:30', courses_8.course_name AS '13:00', courses_9.course_name AS '13:30', courses_10.course_name AS '14:00', courses_11.course_name AS '14:30', 
                         courses_12.course_name AS '15:00', courses_13.course_name AS '15:30', courses_14.course_name AS '16:00', courses_15.course_name AS '16:30'
FROM            classtimes LEFT OUTER JOIN
                         courses AS courses_15 ON classtimes.timec_165 = courses_15.course_id LEFT OUTER JOIN
                         courses AS courses_14 ON classtimes.timec_16 = courses_14.course_id LEFT OUTER JOIN
                         courses AS courses_13 ON classtimes.timec_155 = courses_13.course_id LEFT OUTER JOIN
                         courses AS courses_12 ON classtimes.timec_15 = courses_12.course_id LEFT OUTER JOIN
                         courses AS courses_11 ON classtimes.timec_145 = courses_11.course_id LEFT OUTER JOIN
                         courses AS courses_10 ON classtimes.timec_14 = courses_10.course_id LEFT OUTER JOIN
                         courses AS courses_9 ON classtimes.timec_135 = courses_9.course_id LEFT OUTER JOIN
                         courses AS courses_8 ON classtimes.timec_13 = courses_8.course_id LEFT OUTER JOIN
                         courses ON classtimes.timec_9 = courses.course_id LEFT OUTER JOIN
                         courses AS courses_7 ON classtimes.timec_125 = courses_7.course_id LEFT OUTER JOIN
                         courses AS courses_6 ON classtimes.timec_12 = courses_6.course_id LEFT OUTER JOIN
                         courses AS courses_1 ON classtimes.timec_95 = courses_1.course_id LEFT OUTER JOIN
                         courses AS courses_4 ON classtimes.timec_11 = courses_4.course_id LEFT OUTER JOIN
                         courses AS courses_2 ON classtimes.timec_10 = courses_2.course_id LEFT OUTER JOIN
                         courses AS courses_3 ON classtimes.timec_105 = courses_3.course_id LEFT OUTER JOIN
                         courses AS courses_5 ON classtimes.timec_115 = courses_5.course_id LEFT OUTER JOIN
                         classrooms ON classtimes.classroom_id = classrooms.classroom_id
ORDER BY room, dow

Replace course_id with course_name in results

1 Answers1