Ignore null results in MySQL JOIN queries

I have a MySQL join query which works well (disclaimer: taken from tutorial):

<?php开发者_Python百科
    $connection = mysql_connect("localhost", "root", "test") or die("Error connecting to database");
    mysql_select_db("products90", $connection);
    $result = mysql_query("SELECT * FROM buyers LEFT JOIN products USING (id);", $connection) or die("error querying database");
    $i = 0;
    while($result_ar = mysql_fetch_assoc($result)){
    ?>
    <table>
    <tr <?php if($i%2 == 1){ echo "class='body2'"; }else{echo "class='body1'";}?>>
    <td>
    <?php echo $result_ar['buyer_name']; ?></td>
    <td>
    <?php echo $result_ar['manufacturer']; ?>
    </td>
    <td>
    <?php echo $result_ar['product_name']; ?>
    </td>
    </tr>
    </table>
    <?php
    $i+=1;
    }
    ?>

However, if I wanted it to ignore NULL results, what would I need to do with the JOIN, which type is appropriate?

I had a look on Google, but am not sure where to proceed from here.

By saying you want to ignore NULL results what exactly do you mean? Are you saying that when your left join executes you're getting some buyers which NULL next to them where products would be (had they bought some)?

In that case what you're looking for is an INNER JOIN which will only show values if it has a match in the other.

When you say you have null results, you mean where there are no products?

Anyway, if you have null IDs, therefore, no result in products, you could just also add

where products.id is not null

or, you could change the join from left join to just join, making it an inner join

You would use an INNER JOIN for that (if I got your question right).

For an overview of JOIN types in MySQL please refer to http://dev.mysql.com/doc/refman/5.1/en/join.html

SELECT * FROM buyers LEFT JOIN products USING (id) WHERE products.id IS NOT NULL

OR

SELECT * FROM buyers JOIN products USING (id)

Using JOIN only brings matched rows where as the LEFT JOIN brings all buyers regardless of any matches found in products and filters the records afterwards. I am not exactly sure about your scenario but it seems simpler to me to just use JOIN instead of LEFT JOIN

If you want to ignore the null results then just use the INNER JOIN, you don't need a LEFT JOIN.
Change your query to:

SELECT * FROM buyers JOIN products USING (id)