CODEWITHSUNDEEP
mysqlsql
tttpapi
tttpapi

Reputation: 887

MySQL CASE QUERY with INNER JOIN

I have 1 table with string type. (photo_add, photo_comment etc.) Then I have 4 other tables that are connected with these types.

I need to make a query which will choose the data from all these tables according to the type.

I created this but it does not work.

SELECT DISTINCT a.id, a.event, a.params, a.created_at, a.item_id, a.sender_id, a.receiver_id, u.name, u.id as userid 
FROM `eva49_lovefactory_activity` a, eva49_users u 
CASE a.event 
  WHEN "photo_add" THEN 
    INNER JOIN eva49_lovefactory_photos lp ON lp.id = a.item_id AND lp.fsk18 = -1
  WHEN "group_create" THEN 
     INNER JOIN eva49_lovefactory_groups lg ON lg.id = a.item_id AND lg.fsk18 = -1 
  WHEN "photo_comment" THEN 
     INNER JOIN eva49_lovefactory_item_comments lic ON lic.item_id = a.item_id AND lic.fsk18 = -1 
  WHEN "profile_comment" THEN 
     INNER JOIN eva49_lovefactory_item_comments lic ON lic.item_id = a.item_id AND lic.fsk18 = -1 
  WHEN "profile_comment" THEN 
    INNER JOIN eva49_lovefactory_profiles lp ON lp.user_id = a.receiver_id AND lp.status_fsk18 = -1 
  ELSE 1=1 
END 
WHERE (u.id = a.sender_id OR u.id = a.receiver_id) and (u.id <> 379) 
ORDER BY a.created_at DESC LIMIT 0, 10

Is there any way how to pair the tables? The database structure was given to me like this.

EDIT:

There is a table "eva49_lovefactory_activity" which contains information about recent activity. It has a column "event" that contains string with the event name (photo_add, photo_comment etc.)

There are other tables - eva49_lovefactory_photos, eva49_lovefactory_profiles, eva49_lovefactory_groups etc. In these tables I have to find whether the item was approved by admin. If it was I can show it. (fsk18 = -1)

Upvotes: 2

Views: 1396

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Move CASE condition to JOIN condition and do LEFT JOIN..

SELECT DISTINCT a.id, a.event, a.params, a.created_at, a.item_id, a.sender_id, a.receiver_id, u.name, u.id as userid 
FROM `eva49_lovefactory_activity` a, eva49_users u 

    LEFT JOIN eva49_lovefactory_photos lp ON lp.id = a.item_id AND lp.fsk18 = -1
               AND a.event='photo_add'
    LEFT JOIN eva49_lovefactory_groups lg ON lg.id = a.item_id AND lg.fsk18 = -1 
               AND a.event='group_create'
    LEFT JOIN eva49_lovefactory_item_comments lic ON lic.item_id = a.item_id AND 
              lic.fsk18 = -1     AND a.event='photo_comment'
    LEFT JOIN eva49_lovefactory_item_comments lic ON lic.item_id = a.item_id AND 
              lic.fsk18 = -1     AND   a.event='profile_comment'

WHERE (u.id = a.sender_id OR u.id = a.receiver_id) and (u.id <> 379) 
ORDER BY a.created_at DESC LIMIT 0, 10

Upvotes: 5

Related Questions