rewritten mysql query returning unexpected results, trying to figure out why
- by dq
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 works 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';
