MySQL Where on Multiple Left Join Rows

Question

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:

Order 1 (mage_sales_order)
- 2-sampleA-sku (mage_sales_order_item)
- 2-sampleB-sku (mage_sales_order_item)

Should not be returned:

Order 1 (mage_sales_order)
- 1-sample-sku (mage_sales_order_item)
- 2-sampleA-sku (mage_sales_order_item)
- 2-sampleB-sku (mage_sales_order_item)

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

Why the AND before the mage_sales_order_item.sku NOT LIKE '1-%' ? — Kaushik NP
– Kaushik NP, Commented Oct 18, 2017 at 8:56
@KaushikNP Because I simplified the sql for this question and forgot to remove it (edited now)! — tim.baker
– tim.baker, Commented Oct 18, 2017 at 8:57

Darshan Mehta · Accepted Answer · 2017-10-18 08:58:59Z

1

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;

answered Oct 18, 2017 at 8:58

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2017-10-18 09:00:21Z

0

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.

answered Oct 18, 2017 at 9:00

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Andy · Accepted Answer · 2017-10-18 09:03:08Z

0

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

answered Oct 18, 2017 at 9:03

Andy

3,7742 gold badges25 silver badges34 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

If the SKU starts with 2- then by definition it can't also start with 1-, so at the very least you could remove one of your join conditions.

Collectives™ on Stack Overflow

MySQL Where on Multiple Left Join Rows

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related