'Use of Cases with Where clause in MySQL query

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

mysqlsql


Solution 1:[1]

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")

Solution 2:[2]

As per the guide lines mentioned by @hofan41 i used the below query to get results from two different joins

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_items ON phpfox_news_items.item_id = feed.item_id AND feed.type_id = "news"

Left Join phpfox_news_feeds ON phpfox_news_feeds.feed_id = phpfox_news_items.feed_id

Left Join phpfox_news_categories ON phpfox_news_categories.category_id = phpfox_news_feeds.category_id

Left Join phpfox_pages ON phpfox_pages.page_id = feed.item_id AND feed.type_id = "pages_created"          
LEFT JOIN phpfox_pages_type ON phpfox_pages_type.type_id = phpfox_pages.type_id

Where feed.feed_reference = 0 
AND feed.type_id in ("forum","blog","event","news","video","pages_created") AND (phpfox_news_categories.category_name in ("Movies","music") OR phpfox_pages_type.name in ("Movies","music"))
ORDER BY `feed`.`item_id`  ASC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 mobi001

Related Questions