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, Apr 28 '15 at 03:36

score 2 · Accepted Answer · answered Apr 28 '15 at 03:25

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 '15 at 03:25

potashin

44,205
11
83
107

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. – Filipe Barreto Peixoto Teles Apr 28 '15 at 06:20
@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. – potashin Apr 28 '15 at 06:24
1

Deleted the edit and posted this question: http://stackoverflow.com/questions/29911906/joining-three-tables-on-mysql – Filipe Barreto Peixoto Teles Apr 28 '15 at 06:29

score 1 · Answer 2 · answered Apr 28 '15 at 03:36

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;

score 0 · Answer 3 · answered Apr 28 '15 at 03:29

0

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.

answered Apr 28 '15 at 03:29

sgeddes

62,311
6
61
83

in MySQL `left join/is not null` pattern should give the best performance. – potashin Apr 28 '15 at 03:36
@notulysses -- that's true against `not exists` -- but `exists` should out perform `distinct/inner join`... Good post -- http://stackoverflow.com/questions/12201885/how-to-determine-what-is-more-effective-distinct-or-where-exists – sgeddes Apr 28 '15 at 03:38
Thanks, I'll have a look. – potashin Apr 28 '15 at 03:41

Results from query as argument for WHERE statement in MySQL

3 Answers3