CODEWITHSUNDEEP
phpmysqlwordpress
Raf
Raf

Reputation: 352

Sql query multiple or conditions - which one matched

Is there a way to return the keyword by which the result was found when using multiple or conditions, for example:

  "SELECT post_id FROM wp_posts WHERE 
wp_posts.post_content LIKE '%ABC%' 
OR wp_posts.post_content LIKE '%DEF%' 
OR wp_posts.post_content LIKE '%GHI%'"

This is the original code:

   SELECT DISTINCT wp_posts.ID, pm01.meta_value FROM wp_posts 
  LEFT JOIN wp_postmeta as pm01 ON wp_posts.ID = pm01.post_id AND (
    pm01.meta_key LIKE '_widget_attachments_list_%__file_img' 
    OR 
    pm01.meta_key LIKE '_widget_documents_list_%__link'
    OR
    pm01.meta_key IN ('_the_content', '_widget_text_top', '_widget_text_btm' )
  ) 
   LEFT JOIN wp_postmeta as pm02 ON wp_posts.ID = pm02.post_id AND pm02.meta_key = '_widget_type' 
  WHERE  ( wp_posts.post_type = 'post' OR ( wp_posts.post_type = 'widget' AND pm02.meta_key = '_widget_type' AND ( pm02.meta_value = 'attachments' OR pm02.meta_value = 'documents' ) ) )  
  AND wp_posts.post_status = 'publish' 
  AND
   ( 
    ( pm01.meta_key IN ('_the_content', '_widget_text_top', '_widget_text_btm' ) AND ( pm01.meta_value LIKE '%http://my-site/wp-content/uploads/2018/03/to_do.docx%') )
    OR
    ( pm01.meta_key LIKE '_widget_attachments_list_%__file_img' AND ( pm01.meta_value = 1773) )
    OR
    ( pm01.meta_key LIKE '_widget_documents_list_%__link' AND ( pm01.meta_value LIKE '%http://my-site/wp-content/uploads/2018/03/report2.docx%') )
  )

What I'd like to achieve is to return the post id along with the url if matched or the attachment Id in one case.

Thanks.

Upvotes: 0

Views: 62

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can use a QUERY like this.

SELECT post_id,
    CONCAT_WS(','
    , IF ( wp_posts.post_content LIKE '%ABC%','ABC', NULL )
    , IF ( wp_posts.post_content LIKE '%DEF%','DEF', NULL )
    , IF ( wp_posts.post_content LIKE '%GHI%','GHI', NULL )
    ) AS KEYWORDS
FROM wp_posts
WHERE
   wp_posts.post_content LIKE '%ABC%' 
OR wp_posts.post_content LIKE '%DEF%' 
OR wp_posts.post_content LIKE '%GHI%';

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31802

Since you didn't define the return format, this might be of use for you:

SELECT post_id, x.pattern
FROM wp_posts
JOIN (
    SELECT '%ABC%' as pattern
    UNION ALL 
    SELECT '%DEF%' as pattern
    UNION ALL 
    SELECT '%GHI%' as pattern
) x ON wp_posts.post_content LIKE x.pattern

Upvotes: 0

Steve Westgarth
Steve Westgarth

Reputation: 103

To do this you need to use a SQL case statement 

SELECT post_id
       CASE WHEN wp_posts.post_content like ‘%abc%’ THEN ‘Abc’
            ELSE 
CASE WHEN wp_posts.post_content like ‘%def%’' THEN ‘def’
END
 END AS keyword
  FROM XYZ

I havn’t tested this in SSMS but it should work either as is or with minor syntax tweaks.

Upvotes: 0

Related Questions