Clever Geek Handbook

Query results as argument for WHERE clause in MySQL

I have two tables, orders and orderProducts. They both have a column called "order_id".

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

I want SELECT SKU within date range.

My request so far:

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 request runs, but it returns nothings. What am I missing here?

+3


source to share


3 answers


Try to put the condition date

in a clause where

, no need for a 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'
+2


source


try using clause in where condition for date, 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;
+1


source


There are several options for this. Here I prefer to use 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, join

you may need to use distinct

to eliminate duplicates. Alternatively, you can achieve the same results with in

, but exists

should work better.

0


source






More articles:


All Articles