[SOLVED] Use of Cases with Where clause in MySQL query

Issue

I need to join the tables as per the where clause, basically I have a table of feeds structure is similar to the below, I have only mentioned the necessary columns as there are large number of columns which are unnecessary to show here

feed_id | type_id | user_id | timestamp

I am using the below query to get the result now.

SELECT feed.*
FROM `phpfox_feed` feed
JOIN `phpfox_user` u ON u.user_id = feed.user_id
Left Join `phpfox_friend` f  ON feed.user_id = f.user_id AND f.friend_user_id = 441
Left Join `phpfox_app` apps  ON feed.app_id = apps.app_id
Where feed.feed_reference = 0 
      AND feed.type_id in ("forum","blog","event","news","video","pages_created")

In type_id it shows the result i.e: news, events, group, blog

So I want that if row have 'feed_type' = news then it join with the specific tables, similarly if row have ‘feed_type’ = group it join with other tables, for example I mention the case of news and groups below

Case News:

Join phpfox_news ON phpfox_news.feed_id = feed.feed_id
Join phpfox_news_cateogries 
            ON phpfox_news.cateogry_id = phpfox_news_cateogries.cateogry_id
Where phpfox_news_cateogries.category_name in ("sports","politics","party")

Case Group:

Join phpfox_group ON phpfox_group.feed_id = feed.feed_id
Where phpfox_group.category_name in ("sports","politics","party")

What I tried

I tried the below by using an example I mentioned in where I Stuck, but it’s giving me an error

SELECT feed.*
FROM `phpfox_feed` feed
JOIN `phpfox_user` u ON u.user_id = feed.user_id
Left Join `phpfox_friend` f  ON feed.user_id = f.user_id AND f.friend_user_id = 441
Left Join `phpfox_app` apps  ON feed.app_id = apps.app_id
Where feed.feed_reference = 0 
     AND feed.type_id in ("forum","blog","event","news","video","pages_created") 
  AND               
Case                
   When feed.type_id = "news" 
    Then Join phpfox_news ON phpfox_news.feed_id = feed.feed_id
         Join phpfox_news_cateogries 
             ON phpfox_news.cateogry_id = phpfox_news_cateogries.cateogry_id
          Where phpfox_news_cateogries.category_name in ("sports","politics","party")

   When feed.type_id  = "group" 
    Then Join phpfox_group ON phpfox_group.feed_id = feed.feed_id
        Where phpfox_group.category_name in ("sports","politics","party")
            
End

    

Where I got stuck

What I found is a Cases in SQL query, but I found it only support with select or in join like below example shows which I follow for what I tried above

    Select LNext.player As NextPlayer
    From lineups As L
    Left Join lineups As LNext
    On LNext.BattingOrder Between 11 And 20
        And LNext.BattingOrder  = Case
                                    When L.BattingOrder  = 19 Then 11
                                    Else L.BattingOrder  + 1
                                    End
    Where L.battingOrder Between 11 And 20
    

Query

1. Can this sort of query can be use with the ‘Cases’ in MySQL?

2. How can I use the Case with where clause to achieve my result

Solution

Try joining the tables while adding the condition for the feed.type_id to be the appropriate type.

Instead of doing:

When feed.type_id = "news" Then Join phpfox_news ON phpfox_news.feed_id = feed.feed_id

Try:

Join phpfox_news ON phpfox_news.feed_id = feed.feed_id AND feed.type_id = "news"

So the query you are trying to run should be altered to:

SELECT feed.*
FROM `phpfox_feed` feed
JOIN `phpfox_user` u ON u.user_id = feed.user_id
Left Join `phpfox_friend` f  ON feed.user_id = f.user_id AND f.friend_user_id = 441
Left Join `phpfox_app` apps  ON feed.app_id = apps.app_id
Left Join phpfox_news ON phpfox_news.feed_id = feed.feed_id AND feed.type_id = "news"
Left Join phpfox_news_cateogries ON phpfox_news.cateogry_id = phpfox_news_cateogries.cateogry_id
          AND phpfox_news_cateogries.category_name in ("sports","politics","party")
Left Join phpfox_group ON phpfox_group.feed_id = feed.feed_id AND feed.type_id  = "group" AND  phpfox_group.category_name in ("sports","politics","party")
Where feed.feed_reference = 0 
     AND feed.type_id in ("forum","blog","event","news","video","pages_created") 

Answered By – hofan41

Answer Checked By – Cary Denson (BugsFixing Admin)