CODEWITHSUNDEEP
mysqlsqle-commerce
Oliver Williams
Oliver Williams

Reputation: 6344

most efficient MySQL query to get all items in an order - if any one item meets a criteria

This is pretty much a classic SQL query from an ecommerce standpoint.

I have one table named orders which contains the orderid and date and a unique order number, along with information about the purchaser (just foreign keys to another table) etc..

I also have a table named order_items which contains the item id, the amount, the quantity, as well as a foreign key leading back to the orders table (orders.orderid)

What I want to do is design a query that returns * from the order and * from the item table, for any order which has a specific type item - but as well as the other items which don't match that category.

So for example:

Order 1 bought an apple, banana and daquiri
Order 2 bought a banana, cherry and envelope
Order 3 bought an apple, envelope and fig

I want to return ALL DATA in both the order table and item table if the order contains an apple. So in this case:

order1    apple
order1    banana
order1    daquiri
order3    apple
order3    envelope
order3    fig

Because (obviously) both order 1 and 3 have an apple in the order.

I am especially concerned about the most EFFICIENT way to do this presuming I have all the right indexes.

My best shot so far:

SELECT 
o.*, i.*
FROM items hasItem JOIN orders o ON hasItem.orderid=o.orderid AND hasItem.name='apple'
JOIN items i ON o.orderid=i.orderid
GROUP BY o.orderid, i.itemid

But I would like to avoid the GROUP BY as it seems to be inelegant.

Upvotes: 0

Views: 71

Answers (2)

Jaimie Sirovich
Jaimie Sirovich

Reputation: 1108

If you're worried about dupes, what you want what's called an index subquery or "semijoin." It may actually require a subquery to be expressed in any form (without misusing group-by). There is no "SEMI JOIN" JOIN type. GROUP BY will be faster done at the beginning than at the end like you're doing currently. Your schema and indexes will definitely change performance characteristics.

Upvotes: 0

PM 77-1
PM 77-1

Reputation: 13334

Try the one below. 

SELECT o.*, i.*
FROM orders o
JOIN items i ON  o.orderid=i.orderid
WHERE EXISTS (SELECT 1 
              FROM items 
              WHERE items.name = 'apple' AND o.orderid = items.orderid)
ORDER BY o.orderid, i.name

Upvotes: 1

Related Questions