CODEWITHSUNDEEP
phpmysqljoin
avenas8808
avenas8808

Reputation: 41

Ignore null results in MySQL JOIN queries

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

<?php
    $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.

Upvotes: 4

Views: 10448

Answers (5)

Sabeen Malik
Sabeen Malik

Reputation: 10880

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

Upvotes: 1

BugFinder
BugFinder

Reputation: 17858

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

Upvotes: 2

wonk0
wonk0

Reputation: 13952

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

Upvotes: 1

Chandu
Chandu

Reputation: 82903

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)

Upvotes: 0

m.edmondson
m.edmondson

Reputation: 30872

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.

Upvotes: 2

Related Questions