How to retrieve elements from another table in a SQL query? Sorry for my english !!!


I get data from a table wp_posts and now I need to get data from the table wp_postmeta that match the ID captured in wp_posts table.

The data that I need to grab in the table wp_postmeta are product_img1, product_img2, product_img3 as shown in image below.



This is a view of table wp_posts.



I need to show ID, post_title, product_img1, product_img2, product_img3 and my SQL query is:

require("configs/conxao.php");
$datas = $pdo-&gt;query("SELECT p.ID, p.post_title, pm.product_img1, pm.product_img2, 
                              pm.product_img3 
                          FROM wp_posts p  
                          JOIN wp_postmeta pm 
                          ON p.ID = pm.post_id
                          WHERE p.post_type = 'wpcproduct'
                    ");
$datas-&gt;execute();

while($donnees = $data-&gt;fetch(PDO::FETCH_ASSOC)){

     How to show this data here?

}



  Can you help me to show this data for ID 103?
SELECT 
p.ID, 
p.post_title, 
ProductImage1.product_img1, 
ProductImage2.product_img2, 
ProductImage3.product_img3 
FROM wp_posts p 
cross apply (
        select top(1) pi1.product_img1 
        from wp_postmeta pi1  
        where p.ID = pi1.post_id
        and pi1.meta_key = 'product_img1'
        ) as ProductImage1
cross apply (
        select top(1) pi2.product_img2 
        from wp_postmeta pi2  
        where p.ID = pi2.post_id
        and pi2.meta_key = 'product_img2'
        ) as ProductImage2
cross apply (
        select top(1) pi3.product_img1 
        from wp_postmeta pi3  
        where p.ID = pi3.post_id
        and pi3.meta_key = 'product_img3'
        ) as ProductImage3
WHERE p.post_type = 'wpcproduct'
AND p.ID = 103