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.