How to use Weekday() function inside join in MySQL? I have 2 tables from which one is a parent &amp; other one is child.For 1 order we have multiple records in child table.
For e.g: Order &amp; 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] &amp; 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 &quot;%1%&quot;);

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.
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
...