full outer join sql

Question

I created 2 simple table. tableA and table B, both only have 1 column 'po'. I am trying to combine the 2 table together using FULL OUTER JOIN.

The error output is Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result.

So I guess pretty much my query is wrong. Appreciate any help here!

<?php
include 'db.php';

$sqlcount="SELECT DISTINCT po FROM testonly FULL OUTER JOIN testonly2 ON testonly.po=testonly2.po"; 

$resultcount =  mysqli_query($conn,$sqlcount);
while($row = mysqli_fetch_array($resultcount)){
echo $row['po'];
}
?>

Answer 1

if you need an united table you need and union You can't use FULL OUTER JOIN because this is not available in mysql ..but yuo can emulate with and an union select

The union clause implies that only distint value are merged for the two table ..

      $sqlcount="SELECT po 
                  FROM testonly 
                  UNION 
                  testonly2 "; 

the union all implies the select of all the rows (also duplicated) from both the tables

      $sqlcount="SELECT po 
                  FROM testonly 
                  UNION ALL 
                  testonly2 "; 

otherwise if you need a real join (bothe the column on the same rows ) you can use JOIN (inner join .. or left join, or right ) depending the way you need join the table

this is for inner join

$sqlcount="SELECT DISTINCT t1.po, t2.po 
  FROM testonly t1 INNER JOIN testonly2 t2 ON testonly.po=testonly2.po"; 

Answer 2

There is no FULL OUTER JOIN built in with MySQL. But, you could simulate your query via:

SELECT * FROM testonly t1
LEFT JOIN testonly2 t2 ON t1.po = t2.po
UNION                                     -- use UNION ALL to retain common rows
SELECT * FROM testonly t1
RIGHT JOIN testonly2 t2 ON t1.po = t2.po

PHP code:

$sqlcount = "SELECT * FROM testonly t1 LEFT JOIN testonly2 t2 ON t1.po = t2.po UNION ALL SELECT * FROM testonly t1 RIGHT JOIN testonly2 t2 ON t1.po = t2.po";

Read here for more information: Full Outer Join in MySQL