user3631428 user3631428 - 4 months ago 9
MySQL Question

NULL output with SQL Join

my problem is I received NULL output. What is the problem with my query?

$pr_code = $mysqli->real_escape_string($_POST['pr_code']);
$counter = $mysqli->real_escape_string($_POST['counter']);

$sql = $mysqli->query("
SELECT
a.*,
b.counter, b.pr pr_b
FROM
(SELECT *
FROM pr_list) a
LEFT JOIN
(SELECT counter, pr
FROM purchase_request) b
ON a.pr= b.pr
WHERE a.pr='$pr_code' AND b.counter='$counter'
");

while($row = $sql->fetch_assoc())
{
$pr= $row['pr'];
$approved= $row['approved'];
$requested = $row['requested'];
}

Answer

Move the WHERE clause's b.counter='$counter' to the ON clause if you really want a LEFT JOIN. (Otherwise it's a regular inner join.)

$sql = $mysqli->query("
SELECT 
    a.*,
    b.counter, b.pr pr_b
FROM pr_list a
JOIN purchase_request b 
ON a.pr= b.pr
WHERE a.pr='$pr_code' AND b.counter='$counter'
");

Hope this will help

Comments