0

I am new to SQL and currently creating a custom report page for a local moodle install. I have had success joining tables to get the information I need, but this logs table (in the graphic) has me stumped. I would like to show in my query the "human" name of the objectID using a join, but there are many duplicate objectID's that relate to data found in different tables. The objecttable column shows the table name the objectID is located/related to, but how should I approach creating joins that takes both the objecttable and objectID into consideration?

Thanks, Jamie.

Snippet of moodle logs table

Jamie Sandalls
  • 25
  • 5
  • If I'm understanding correctly you are going to have to join in each table: `LEFT OUTER JOIN role_capabilities ON thistable.objectid = role_capabilities.objectid AND thistable.objecttable = 'role_capabilities'` (rinse and repeat for each `objecttable` table) and then in your SELECT use a `coalesce` like `COALESCE(role_capabilities.object_name, user.object_name, feedback_completed.object_name, quiz.object_name, etc...) AS object_name`. I wouldn't expect this to be quick, but that is the drawback of a mult-table-EAV design. Lots of flexibility but slow queries that are painful to write. – JNevill Apr 13 '23 at 16:28
  • I will read up on the LEFT OUTER JOIN and COALESCE statements and give this a try. Thanks for your response! – Jamie Sandalls Apr 13 '23 at 16:32
  • Quick update. I see the question has been closed but JNevill's response was exactly what was needed, and much more succint than the related question. +1 – Jamie Sandalls Apr 14 '23 at 09:40
  • Glad to hear it. I read the suggested duplicate that was linked and was worried it may not be specific enough for your situation. Glad that I was able to squeeze enough into a comment to get you headed in the right direction! – JNevill Apr 14 '23 at 13:54

0 Answers0