Can I use join instead of a subquery which returns 1 row?Ask Questions

 Posted on 31/07/2018 8:22:49 AM

Here is my query:

SELECT p1.*, (select guid from wp_posts p2 where p2.post_parent = p1.id ORDER by p2.id DESC LIMIT 1) as post_image

from wp_posts p1 where p1.post_status = 'publish' and p1.post_type = 'post' order by p1.id DESC limit 4

I want to make the same query but using join (instead of subquery). Is it possible ?

 

Share On: facebook gplus twitter
profile
Asked by Hardik Bera on 31/07/2018 8:22:49 AM Score: 375 points
Add Comment:
Submit

Comments

1 Answer

1 Corrected Answers
Aproved Answers
1
Profile
Answered by ketan Linbachiya on Aug 2 2018 8:54AM Score: 269 points

We can try rewriting using a series of extra joins:

SELECT
    p1.*,
    COALESCE(t2.guid, 'NA') AS post_image
FROM wp_posts p1
LEFT JOIN
(
    SELECT post_parent, MAX(id) AS max_id
    FROM wp_posts
    GROUP BY post_parent
) t1
    ON p1.id = t2.post_parent
LEFT JOIN wp_posts t2
    ON t1.max_id = t2.id
WHERE p1.post_status = 'publish' AND p1.post_type = 'post'
ORDER BY p1.id DESC
LIMIT 4;
Add Comment:

Post Your Answer

  •  
  •  

Existing Members

Sign in to your account
Email Address
Password
...or Join us
Download, Vote, Comment, Publish.
Full Name
Email Address
I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the StoodQ newsletters
Guideline to answer a question:

Useful tips to submit your answer
Please read below guidelines before you submit your answer for question.

  • Read and understand question for which you are submitting your answer.
  • Try to avoid grammatical and spell mistake while answering.
  • Do not post any irrelevant information in your answer.
  • Explain your answer with example or any reference link to help who posted question.
  • If you find irrelevant question, please report it to support. Click here to contact support.
  • You agree to the privacy policy and terms of use to submit any contents.

Note: StoodQ is online developers community which helps developer for their difficulty, lets help them with your value contribution.