How to exclude records which don't match a LEFT JOIN from a SELECT in MySQL?

Question

I'm trying to get a product search to work properly in MySQL 5.0.88.

Basic setup: I have table A with products

A.id
A.ean
A.styleNo
A.price
A.seller (ID e.g. 123, 456)

And a table B with pricelists

B.name
B.ean
B.alt_price
B.seller

Sellers can define optional pricelists, which are matched to the user doing the search. My search more or less looks like this:

    SELECT A.styleNo, A.price, B.price
    FROM arts A
        LEFT JOIN pricelists B ON
                B.seller = A.seller 
            AND B.ean = A.ean 
            AND B.alt_price != 0
    WHERE...
    // pricelists
    AND ( B.name = "some_name" AND B.seller = "123" ) OR ( next applicable seller ) ...

So after the LEFT JOIN, I'm including all items from a pricelist by name and seller.

This works ok as it selects both the regular price (A) and alt_price (B) and I can check for existing alt_price when displaying the results to show the correct price to the user.

However if the seller does not give an alt-price to all of his products, I'm now displaying the product with the price from A, when in fact I DON'T want to display products from this seller which do not have a pricelist entry at all (think regional assortment).

So if user X has a pricelist "abc" and seller 123 has 500 products, 200 of which are on the pricelist "abc", I only want to display the 200 products and not 500 with 200 in the correct price.

I tried to use B.alt_price != 0 in the LEFT JOIN, but this doesn't help, because all items on there have a price.

Question
Is there a way to do this in the actual search or do I have to do it in the results loop, which I'm not really keen on doing.

Solution 1

Ok. Finally found the solution.

The problem is, the search will contain multiple sellers, some of whome use pricelists and some who don't.

• If I do an INNER JOIN on pricelists B, I will not get the products of sellers who don't use pricelists as they will not be have any entry in B.
• If I do a LEFT JOIN on pricelists B, all entries will have either NULL or pricelist B values, so when I'm searching for a seller who uses pricelists for some of his products, I will always get his full range, regardless of alt_price specified
• If I try to filter these unwanted records (seller uses pricelists, exclude products which are not on it), by adding B.alt_price != 0 to the WHERE clause, I'm also excluding all products from sellers not using pricelists.

I solved it like this: - I'm having to construct this line anyway:

LEFT JOIN pricelists B ON
       // dynamic construct depending on number of sellers using pricelists matched to user
            B.seller = A.seller 
        AND B.ean = A.ean 
        AND B.alt_price != 0

• So I created another variable, that includes all seller IDs who use pricelists and are applicable to this user. Looks like this:

  123,456,789...

• I add this to the WHERE clause:

  AND ( IF( A.seller IN ( 123,456,789... ), B.alt_price IS NOT NULL,1 ) )

This way, I'm checking
(a) if the record is from a pricelist seller applicable to the user, and
(b) if that's the case, the records must not have a NULL value in the b.alt_price, which records not being on the pricelist will have, since sql adds NULL to all records not on the pricelist B when LEFT JOINING.

That was difficult...

Solution 2

SELECT 
    b.styleNo, b.price, a.alt_price
FROM
    pricelists a
INNER JOIN
    arts b ON a.seller = b.seller AND a.ean = b.ean
WHERE
    a.alt_price <> 0 AND
    a.name = 'name' AND
    a.seller = 123

What the INNER JOIN is doing here is returning the row only if the seller and ean fields match in both tables, so it will only retrieve the products which are on the pricelist filtered through on the WHERE.

A LEFT JOIN on the other hand, will return all rows regardless of whether or not there's a match in the other table. If there is a match, the corresponding values in the second table are shown, but if there isn't, the values will be NULL from the second table, while still retaining the row data from the first table.

So if we instead did FROM arts a LEFT JOIN pricelists b ON ..., we would get all rows from the products table regardless of whether there's a match in the pricelist table. If a pricelist didn't match up, the product still shows, but with the pricelist data containing NULL values.

Note that the table on the left side of the LEFT JOIN has its row data retained regardless of matches in the table on the right side of the LEFT JOIN... hence "LEFT".

You might want to take a look at Jeff Atwood's visual explanation of joins to understand how the different JOIN types work.

Also understand that WHERE is evaluated after joins, so the conditional filtering you specify in WHERE will apply after the joins have taken place. So if you wanted all rows only where table2's rows didn't match table1's rows in a LEFT JOIN, you would specify WHERE table2.fieldname IS NULL.