CODEWITHSUNDEEP
mysqlleft-joinwhere-clause
tim.baker
tim.baker

Reputation: 3307

MySQL Where on Multiple Left Join Rows

I have a MySQL Left Join Query that returns 1 row from table A and multiple rows from table B. I need to have multiple WHERE clauses on these rows and only return if all of the B rows match.

I have:

SELECT 

mage_sales_order.entity_id

FROM mage_sales_order 

LEFT JOIN mage_sales_order_item 
ON mage_sales_order.entity_id = mage_sales_order_item.order_id 

WHERE 

 mage_sales_order_item.sku NOT LIKE '1-%' 
 AND mage_sales_order_item.sku LIKE '2-%' 

 ## Group order IDs together 
 GROUP BY entity_id

However this will return any order that has an item with the SKU starting with "2-" even if it has an item starting with "1-". I want it so that orders only with items starting with "2-" are returned.

For example:

Should be returned:

Should not be returned:

I assume this has already been asked but trying to search for it is proving difficult.

Upvotes: 1

Views: 39

Answers (3)

Andy
Andy

Reputation: 3743

You can simply move the conditions from the WHERE clause into the JOIN :

SELECT mage_sales_order.entity_id
FROM mage_sales_order 
LEFT JOIN mage_sales_order_item 
ON 
   mage_sales_order.entity_id = mage_sales_order_item.order_id 
   AND mage_sales_order_item.sku NOT LIKE '1-%' 
   AND mage_sales_order_item.sku LIKE '2-%' 
WHERE mage_sales_order_item.order_id IS NOT NULL
GROUP BY entity_id

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

I think you need to move the logic in the WHERE clause to a HAVING clause:

SELECT
    t1.entity_id
FROM mage_sales_order t1
LEFT JOIN mage_sales_order_item t2
    ON t1.entity_id = t2.order_id
GROUP BY
    t1.entity_id
HAVING
    SUM(CASE WHEN t2.sku NOT LIKE '2-%' THEN 1 ELSE 0 END) = 0;

The logical problem with your current WHERE clause is that this logic gets applied to individual records. It makes no sense that say that the SKU should start with 2- and also not start with 1-, since the former condition already guarantees the latter. But, taken in the context of an aggregation over entities, the logic does seem to make sense.

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30819

You can add another sub query to exclude those rows, e.g.:

SELECT mage_sales_order.entity_id
FROM mage_sales_order 
LEFT JOIN mage_sales_order_item 
ON mage_sales_order.entity_id = mage_sales_order_item.order_id 
WHERE mage_sales_order_item.sku LIKE '2-%' 
AND mage_sales_order_item.order_id NOT IN (
    SELECT order_id FROM mage_sales_order_item WHERE sku LIKE '1-%'
)
GROUP BY entity_id;

Upvotes: 1

Related Questions