How to retrieve elements from another table in a SQL query?

Question

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->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->execute();

while($donnees = $data->fetch(PDO::FETCH_ASSOC)){

     How to show this data here?

}

Can you help me to show this data for ID 103?

Answer 1

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

Answer 2

This will work for you:

require("configs/conxao.php");
$datas = $pdo->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' AND p.ID=103
                ");
$datas->execute();

while($donnees = $data->fetch(PDO::FETCH_ASSOC)){
echo("id=".$donnees->ID);
echo("post title=".$donnees->post_title);
echo("product_img1=".$donnees->product_img1);
echo("product_img2=".$donnees->product_img2);
echo("product_img3=".$donnees->product_img3);
}