Using Substring_Index with data from a second table

Question

As a follow up to my previous question:

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

post_type = "answer"
post_type = "question"
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

Luuklag · Accepted Answer · 2020-09-30 12:55:01Z

3

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

edited Sep 30, 2020 at 12:55

Luuklag

3,91412 gold badges40 silver badges59 bronze badges

answered Sep 30, 2020 at 12:46

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2020-09-30 12:56:25Z

2

You need a subquery that returns the ids of the table ap_qa:

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

Or without the table ap_qa:

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

edited Sep 30, 2020 at 12:56

answered Sep 30, 2020 at 12:37

forpas

165k10 gold badges51 silver badges85 bronze badges

Comments

nbk · Accepted Answer · 2020-09-30 12:31:08Z

0

Your systqax is wrong as you error message indicates Use something like zhis

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

answered Sep 30, 2020 at 12:31

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

Using Substring_Index with data from a second table

As a follow up to my previous question:

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

As a follow up to my previous question:

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related