Results from query as argument for WHERE statement in MySQL

Question

I have two tables: orders and orderProducts. They both have a column called 'order_id'.

orders has a column named 'date_created' ordersProducts has a column named 'SKU'

I want to SELECT SKUs in within a date range.

My query so far is:

SELECT `SKU` FROM `orderProducts` INNER JOIN orders ON orderproducts.order_id = orders.order_id WHERE orders.order_id in (SELECT id FROM orders WHERE date_created BETWEEN '2014-10-01' AND '2015-03-31' ORDER BY date_created DESC)

The query runs but it returns nothings. What am I missing here?

In this case you should use the date condition directly in the where clause and get rid of the sub query, but when you do need a subquery, the first step of debuging will be to try each query separatly and only when you get them to work combine them. — Zohar Peled
– Zohar Peled, Commented Apr 28, 2015 at 3:36

souslov · Accepted Answer · 2015-04-28 03:25:56Z

2

Try putting date condition in the where clause, there is no need for the subquery:

select op.`SKU` from `orderProducts` op join `orders` o using(`order_id`) where o.`date_created` between '2014-10-01' and '2015-03-31'

answered Apr 28, 2015 at 3:25

souslov

44.7k11 gold badges92 silver badges113 bronze badges

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

3 Comments

Filipe Barreto Peixoto Teles Over a year ago

Thanks. Changing the question a bit, what is the correct way if I need to join another table (so three in total)? I'll edit my original question with the code I got now.

souslov Over a year ago

@iamfbpt : Instead of editing a post with another question better post a new question, as editing the question in the same post invalidates the answers that were already given.

Filipe Barreto Peixoto Teles Over a year ago

Deleted the edit and posted this question: stackoverflow.com/questions/29911906/…

Sindhoo Oad · Accepted Answer · 2015-04-28 03:36:20Z

try using between clause in your where condition for date, there is no need to use subquery.

SELECT `SKU` FROM `orderProducts` INNER JOIN orders ON orderproducts.order_id = orders.order_id WHERE date_created BETWEEN '2014-10-01' AND '2015-03-31' ORDER BY date_created DESC;

sgeddes · Accepted Answer · 2015-04-28 03:29:54Z

There are several options for this. Here's one I prefer using exists:

select sku from orderproducts op where exists ( select 1 from orders o where o.orderid = op.orderid and o.datecreated between '2014-10-01' and '2015-03-31')

When using a join, you may need to use distinct to eliminate duplicates. Alternatively, you could achieve the same results with in, but exists should perform better.

in MySQL left join/is not null pattern should give the best performance.
@notulysses -- that's true against not exists -- but exists should out perform distinct/inner join... Good post -- stackoverflow.com/questions/12201885/…

Collectives™ on Stack Overflow

Results from query as argument for WHERE statement in MySQL

3 Answers 3

3 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Linked

Related