Search code examples
mysqlsqlselectsubquerywhere-clause

Using Substring_Index with data from a second table

As a follow up to my previous question:

I want to select everything in the wp_posts table that matches:

  1. post_type = "answer"
  2. post_type = "question"
  3. post_type contains revision, preceded by the ID of either one of the previous criteria. For example: 21-revision-v1 or 10903-revision-v1 Where I want to select those posts of which the first numerical part matches the ID of posts selected in the previous 2 requirements.

I now constructed a new table ap_qa which holds all the ID's from posts matching either criteria 1 or 2 above.

Now to select the cases that match criteria 3 I thought of using Substring_Index() as that allows for matches within a string.

My current code is:

SELECT * 
FROM `wp_posts` p 
WHERE p.post_type IN ('answer', 'question') OR
Substring_Index(p.post_Type,'-revision',1) IN QA.ID

The first rule following where is to satisfy criteria 1 and 2, the last row is meant to satisfy criteria 3. However my syntax is invalid, as is returned.

The error message reads (in Dutch):

#1064 - Er is iets fout in de gebruikte syntax bij 'QA.ID' in regel 4

Solution

  • I now constructed a new table ap_qa which holds all the ID's from posts matching either criteria 1 or 2 above.

    You don't at all need a temp table for this. You can get the result that you want directly from the original table in a single query:

    select *
from wp_posts wp
where post_type in ('answer', 'question') and exists (
    select 1
    from wp_posts wp1
    where 
        wp1.post_type in ('answer', 'question') 
        or wp1.id = substring_index(wp.post_type, '-revision', 1)
)