Compare 2 tables and select data from one table

Question

I have 2 tables lc_details and reference, and those have reference_no columns. I want to compare those tables and select reference_no from the reference table if lc_details.reference_no does not exist in reference.reference_no column.

Here is my code but it shows all data from reference table and repeat more times.

        <table class="table table-striped">
        <tr>
            <th>Sl. No.</th>
            <th>Reference No./th>

        </tr>
        <?php
        include("../db/db.php");

        $sql_cat="select b.* from lc_details a, reference b where b.reference_no!=a.reference_no' order by reference_no ASC";
        $run_sql=mysqli_query($con, $sql_cat);

        $i=0;

        while($row_cat=mysqli_fetch_array($run_sql)){
            $reference_no=$row_cat['reference_no'];

            $i++;


        ?>

        <tr align="center">
            <td><?php echo $i; ?></td>
            <td><?php echo $reference_no; ?></td>
           </tr>
           <?php } ?>
        </table>

score 2 · Accepted Answer · edited May 23 '17 at 12:23

Try this:

SELECT ref.* 
FROM reference AS ref
LEFT JOIN lc_details AS lc_d
ON lc_d.reference_no = ref.reference_no
WHERE lc_d.reference IS NULL
ORDER BY ref.reference_no ASC

And let me explain it:

Joining a table using left join means that if the right table(in our example the lc_details table) value or reference does not exists in the left table(in our example the 'reference' table) it will still get the record. for example:

if table A contains ids of 1, 2, 3, and 4 while table B contains ids of 1,2 and 4 only, joining them using left join would yield something like:

A.id   B.id
1      1
2      2
3      NULL
4      4

so adding a where clause that checks only those with null id on the table b will result to:

A.id   B.id
3      NULL

to learn more about joining tables and their differences, try this link

Compare 2 tables and select data from one table

1 Answers1