How to choose inner join field with IF/case?

问题:

Bit of a tricky one.

I am in the middle of cleaning up some data but because of the size of the data, it's going to take quite a while to do and thus I need the app to run while the upgrade is performing. So, I have a comments table and a photos table. Each comment record has either the photo_d_id stored in object_id or the photo_id stored in object_id:

if comment_type = 8 then object_id = photo.photo_p_id
if comment_type = 17 then object_id = photo.photo_id

I need to inner join the photo table in the query but need to perform the innerjoin on the correct field from the photo table based off the comment_type. This isn't possible, but gives an example of what I want to do:

select *.comments,*.photos 
FROM comments
CASE
   when comments.comment_type = 8 then inner join photos on comments.object_id = photo.photo_p_id
   when comments.comment_type = 17 then inner join photos on comments.object_id = photo.photo_id

Any ideas?

回答1:

Try this query (edited) -

SELECT c.*,
  CASE c.comment_type WHEN 8 THEN p1.photo_p_id WHEN 17 THEN p2.photo_id ELSE NULL END photo_id,
  CASE c.comment_type WHEN 8 THEN p1.column1 WHEN 17 THEN p2.column1 ELSE NULL END column1
FROM comments c
  LEFT JOIN photos p1
    ON c.object_id = p1.photo_p_id
  LEFT JOIN photos p2
    ON c.object_id = p2.photo_id

One more variant -

SELECT c.*, p.*
FROM comments c
  JOIN photos p
    ON c.comment_type = 8 AND c.object_id = p.photo_p_id OR c.comment_type = 17 AND c.object_id = p.photo_id