I have a query in which I want to select all the customers who have orders with a particular product_id(say 12,13). I have made a fiddle for this. There is an orders table which contains the order_id and customer_id. Order_detail table has order_id and product_id. These are the sample tables. Original tables contain more than 30000 records. Could you please help me in optimising the queries or guide me the way to do so?
Add a comment
|
2 Answers
You can use below final query-
SELECT customer_id,first_name
FROM customer_detail cd
JOIN orders ors ON cd.customer_id=ors.customer_id
JOIN order_detail od ON od.order_id=ors.order_id
WHERE product_id IN (12,13);
Note: Below fields should be indexed - customer_id IN orders TABLE order_id AND product_id IN order_detail TABLE
4 Comments
Code Guru
what if customers are repeated in orders ?
Code Guru
how can I create view of this in procedure so that I can use this for other joins to get data from other tables?
Zafar Malik
use group by customer_id at the end for multiple customers.
deroby
Please do NOT use GROUP BY or DISTINCT to get rid of double entries. Rather use a WHERE EXISTS construction. Performance wise it's night and day.
select c.* from customer_detail c
inner join orders o on o.customer_id = c.customer_id
inner join order_detail od on od.order_id = o.order_id
where od.product_id in (12,13);
I think this is what you are looking for.
