'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'];
  }
phpmysqlsqlmysqli


Solution 1:[1]

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

Solution 2:[2]

This will give you no nulls for purchase_request values.

A left join SPECIFICALLY ALLOWS nulls for b. So we use an 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'
");

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 yara
Solution 2 Hogan

Related Questions