Conditional columns in MySQL that need to do joins

Question

I've researched related questions on the site but failed to find a solution. What I have is a user activity table in MySQL. It lists all kind of events of a user within a photo community site. Depending on the event that took place, I need to query certain data from other users.

I'll explain it in a more practical way by using two examples. First, a simple event, where the user joined the site. This is what the row in the activity table would look like:

event: REGISTERED
user_id: 19 (foreign key to user table)
date: current date
image_id: null, since this event has nothing to do with images

It is trivial to query this. Now an event for which extra data needs to be queried. This event indicates a user that uploaded an image:

event: IMAGEUPLOAD
user_id: 19 (foreign key to user table)
date: current date
image_id: 12

This second event needs to do a join to the image table to get the image URL column from that table. A third event could be about a comment vote, where I would need to do a join to the comments table to get extra columns.

In essence, I need a way to conditionally select extra columns (not rows) per row based on the event type. This is easy to do when the columns come from the same table, but I'm struggling to do this using joins from other tables. I hope to do this in one, conditional query without the use of a stored procedure.

Is this possible?

Answer 1

You could make the joins depend on the event type, like:

 select     *
 from       Events e
 left join  Image i
 on         e.event = 'IMAGEUPLOAD'
 and        e.image_id = i.id
 left join  comments c
 on         e.event = 'COMMENT'
 and        e.comment_id = c.id

If there's one column that is shared among all linked tables, for example create_date, you can coalesce to select the one that's not NULL:

select      coalesce(i.create_date, c.create_date, ...) as create_date

Answer 2

Doing precisely what you want to do is not possible. A SELECT is designed to return a list of tuples/rows, and each has the same number of elements/columns.

What you really are doing here is collecting 2 different kinds of information, and you're going to have to process the 2 different kinds of information separately anyway, which should be a hint that you're doing something slightly wrong. Instead, pull the various event types out individually, perform whatever additional operations you need to do to convert them to your common output type (eg. HTML if this is for a website), and then interleave them together at that stage.