[SOLVED] NULL output with SQL Join

Issue

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

Solution

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

Answered By – yara

Answer Checked By – Katrina (BugsFixing Volunteer)