Search code examples
mysqlselectjoinunionsql-like

MYSQL Union and LIKE

Hey Guys I'm having difficulty adding LIKE into my UNION, it seems to either return anything, or return just the items specified in the UNION depending on the position of my like, example

Full query

SELECT  fruits.key, 
        region, location, report_date, 
        inspection_type AS type, 
        customer AS customer_name, 
        customer_number, shipper, po

    FROM reports

JOIN (      ( SELECT `key` , `report_key`, `shipper`, `po`, `commodity`, `label`, `status`, `location` FROM `berries`       )
    UNION   ( SELECT `key` , `report_key`, `shipper`, `po`, `commodity`, `label`, `status`, `location` FROM `melons`        )
    UNION   ( SELECT `key` , `report_key`, `shipper`, `po`, `commodity`, `label`, `status`, `location` FROM `citrus`        )
    UNION   ( SELECT `key` , `report_key`, `shipper`, `po`, `commodity`, `label`, `status`, `location` FROM `table_grapes`  )
    UNION   ( SELECT `key` , `report_key`, `shipper`, `po`, `commodity`, `label`, `status`, `location` FROM `tree_fruit`    )
    UNION   ( SELECT `key` , `report_key`, `shipper`, `po`, `commodity`, `label`, `status`, `location` FROM `lot`           )
    ) fruits 

ON inspection_number = fruits.report_key

WHERE fruits.status = '0' OR fruits.status = '1'

ORDER BY report_date DESC

returns

key     region              location        report_date     type    customer_name   customer_number     shipper     po
17      Great White North   South Holywood  2012-10-21      citrus  Name            206-420-9564        Yao Ming    4215
16      Great White North   Boulder, CO     2012-10-21      citrus  Name            206-420-9564        Hanjin      33215
21      nw                                  1969-12-31      citrus  2               321                 sdfg        sdfgs
20      nw                  sdfgsdfg        1969-12-31      citrus  2               321                 sdfg        sdfg

Trying this though

WHERE fruits.status = '0' OR fruits.status = '1' AND fruits.location LIKE '%Boulder%'

Returns the exact same instead of just the row with Boulder in location.

This on the otherhand will return the row, but not the rest of the information from reports

`WHERE fruits.location LIKE '%Bolder%' AND fruits.status = '0' OR fruits.status = '1'

key     region              location        report_date     type    customer_name   customer_number     shipper     po
16      Great White North   Boulder, CO     2012-10-21      citrus  Jd Daniel       206-420-9564        Hanjin      33215

What is the correct way to do this? I seem to get half of one, or none of the other?

Solution

  • Your problem is that AND binds harder than OR, which means your where clause ends up as

    WHERE fruits.status = '0' OR 
     (fruits.status = '1' AND fruits.location LIKE '%Boulder%')

    What you mean to do is probably

    WHERE (fruits.status = '0' OR fruits.status = '1') 
   AND fruits.location LIKE '%Boulder%'

    which means you'll have to add the parentheses around the OR expression.