Code:

$search = $_POST['search'];
$query01 = "(SELECT `product_id` AS `id` FROM `table01` WHERE code=:search) UNION (SELECT `product_id` AS `id` FROM `table02` WHERE code=:search)
 UNION (SELECT `product_id` AS `id` FROM `table03` WHERE code=:search)";
$stmt01 = $db->prepare($query01);
$stmt01->bindValue(':search',$search);
$stmt01->execute();
$count = $stmt01->rowCount();
if($count >= 1){
while($row01 = $stmt01->fetch(PDO::FETCH_ASSOC)){
$id = $row01['id'];

$query02 = "(SELECT * FROM `products` WHERE product_id=:id)";
$stmt02 = $db->prepare($query02);
$stmt02->bindValue(':id',$id);
$stmt02->execute();
while($row02 = $stmt02->fetch(PDO::FETCH_ASSOC)){
$name = $row02['name'];
$price = $row02['price'];

echo '<p>'.$name.'<-->'.$price.'</p>';

}
}
}