rewritten mysql query returning unexpected results, trying to figure out why

I created a messy query in a hurry a while ago to get a list of product codes. I am now trying to clean up my tables and my code. I recently tried to rewrite the query in order for it to be easier to use and understand. The original query wor开发者_StackOverflowks great, but it requires multiple search strings in order to do one search because it uses UNIONS, and it has a few other issues. My newly modified query is easier to understand, and only requires one search string, but is returning different results. Basically the new query is leaving records out, and I would like to understand why, and how to fix it. Here are the two queries (search strings are all null):

Original Query:

$query = 'SELECT product_code FROM bus_warehouse_lots WHERE status=\'2\''.$search_string_1
    .' UNION SELECT product_code FROM bus_po WHERE status=\'0\''.$search_string_2
    .' UNION SELECT bus_warehouse_entries.new_product_code AS product_code FROM (bus_warehouse_entries LEFT JOIN bus_warehouse_transfers ON bus_warehouse_entries.picking_ticket_num=bus_warehouse_transfers.pt_number) LEFT JOIN bus_warehouse_lots ON bus_warehouse_entries.ebooks_lot_id=bus_warehouse_lots.id WHERE bus_warehouse_entries.type=\'6\' AND bus_warehouse_transfers.status=\'0\''.$search_string_3
    .' UNION SELECT bus_contracts.main_product AS product_code FROM bus_contracts LEFT JOIN bus_warehouse_lots ON bus_contracts.main_product=bus_warehouse_lots.product_code WHERE bus_contracts.status=\'0\''.$search_string_4
    .' UNION SELECT prod_id AS product_code FROM bus_products WHERE last_usage > \''.date('Y-m-d', strtotime('-12 months')).'\''.$search_string_5
    .' ORDER BY product_code';

New Query:

$query = 'SELECT bus_products.prod_id FROM bus_products'
    .' LEFT JOIN (bus_warehouse_lots, bus_po, bus_warehouse_entries, bus_contracts) ON ('
    .'bus_products.prod_id = bus_warehouse_lots.product_code'
    .' AND bus_products.prod_id = bus_po.product_code'
    .' AND bus_products.prod_id = bus_warehouse_entries.new_product_code'
    .' AND bus_products.prod_id = bus_contracts.main_product)'
    .' LEFT JOIN bus_warehouse_transfers ON'
    .' bus_warehouse_entries.picking_ticket_num = bus_warehouse_transfers.pt_number'
    .' WHERE (bus_products.last_usage > \''.date('Y-m-d', strtotime('-12 months')).'\''
    .' OR bus_warehouse_lots.status = \'2\''
    .' OR bus_po.status = \'0\''
    .' OR (bus_warehouse_entries.type = \'6\' AND bus_warehouse_transfers.status = \'0\')'
    .' OR bus_contracts.status = \'0\')'
    .$search_string_6
    .' GROUP BY bus_products.prod_id'
    .' ORDER BY bus_products.prod_id';

---

Although I don't know your code language, but looks like PHP, please excuse my lack of .$ string formatting. I know you were using escape sequences for the quotes around static values, such as status, type, etc, I stripped just for simple readability.

When I know that I will be joining tables and expect entries on both sides (hence the LEFT JOIN), I skip that and put that as my direct WHERE clause and list all the tables directly in the FROM. Additionally, for MySQL performance, I tell it to do the query in the order I've stated (via the keyword STRAIGHT_JOIN )

SELECT STRAIGHT_JOIN
        DISTINCT bus_products.prod_id 
    FROM
        bus_products,
        bus_warehouse_lots, 
        bus_po, 
        bus_warehouse_entries, 
        bus_contracts,
        bus_warehouse_transfers
    WHERE 
            bus_products.prod_id = bus_warehouse_lots.product_code
        AND bus_products.prod_id = bus_po.product_code
        AND bus_products.prod_id = bus_warehouse_entries.new_product_code
        AND bus_products.prod_id = bus_contracts.main_product
        AND bus_warehouse_entries.picking_ticket_num = bus_warehouse_transfers.pt_number
        AND (       bus_products.last_usage > {yourDateStringFormatted}
                OR  bus_warehouse_lots.status = '2' 
                OR  bus_po.status = '0'
                OR (    bus_warehouse_entries.type = '6' 
                    AND bus_warehouse_transfers.status = '0')
                OR bus_contracts.status = '0'
            ) 
            { plus your $search_string_6  }
     ORDER BY
           bus_products.prod_id

I would strip our your Last_Usage date clause and your search string 6 to ensure you are at least getting the "ALL" expected entries. If you are still out, ensure your OR/AND conditions are properly balanced where expected. THEN, add back in your date restriction, make sure thats ok, then add in your final Search String 6.

---

This is dq again, not sure how to log back in as myself since I was a guest. Anyways...

I see what you are saying about using ANDs to determine the table relationships, but wouldn't this only return results that match all of the AND criteria? Wouldn't this not return product ids where there is no matching transfer records? Sometimes there are product ids that can find a match in one of the columns, but there are no matches in any of the other columns. I want the query to pull records that can find a matching product id in any one of the other tables mentioned, but not return records that cannot find a matching table in any of the other tables. Is this still the best way to go?