0

I have 2 tables from which one is a parent & other one is child.For 1 order we have multiple records in child table.

For e.g: Order & order_frequency are 2 tables.

Order table data:

+-----+---------+---------------------+
| id  | product | orderDate           |
+-----+---------+---------------------+
| 100 | abc     | 2020-10-01 05:30:00 |
+-----+---------+---------------------+
| 200 | xyz     | 2020-10-01 05:30:00 |
+-----+---------+---------------------+

order_frequency table data:

+----+-------+-----------+
| id | ordID | frequency |
+----+-------+-----------+
| 1  | 100   | 111       |
+----+-------+-----------+
| 2  | 100   | 234       |
+----+-------+-----------+
| 3  | 200   | 111       |
+----+-------+-----------+
| 4  | 200   | 234       |
+----+-------+-----------+

I want to fetch matching frequency from order_frequency table based on order date(Which are falling on monday[111] & Tue,wed,thru[234]).

I have tried below query:

SELECT * FROM order LEFT JOIN order_frequency ON (order.id=order_frequency.ordID AND order_frequency.frequency like "%1%");

when i tried above query by providing hard coded value for frequency in column it provide me expected result.

But i want to fetch it dynamically by providing orderDate. something like below:

SELECT (weekday(DATE(orderDate))+1) AS weekday,* from order LEFT JOIN order_frequency ON (order_frequency.Day like CONCAT('%',@weekday,'%'))

Thanks in advance.

0
SELECT ...
FROM order o
JOIN order_frequency of ON CASE o.frequency 
                           WHEN 111 THEN WEEKDAY(of.orderDate)=0 
                           WHEN 234 THEN WEEKDAY(of.orderDate) BETWEN 1 AND 3
                           END
...
| improve this answer | |
  • Thanks Akina. This query will work but in my case there are 120+ rows in order_frequency table against single order. Is any other solution? – Sanjay Surve Oct 7 at 6:18
  • @SanjaySurve Formulate additional criteria which allows to select some (or even one) row from order_frequency for each row from order. And add it into either ON clause or WHERE clause. You're the only who may do this. – Akina Oct 7 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.